Use XML data type in SQL Server

Practical T-SQL scripts examples to manage XML data in SQL Server.

How to use the XML data type in SQL Server tables ? To start, XML stands for eXtensible Markup Language, it is a standard IT format for storing and exchanging data across various heterogenous platforms. T-SQL (Transact-SQL), the SQL extension from Microsoft, provides extensive support for XML data, including the ability to query, modify, and validate XML data.

Table of Contents

1. Create and fill the table used in the T-SQL XML scripts

Let’s consider the following sample table to manage and store products for this tutorial.

CREATE TABLE Products(
    ProductID int PRIMARY KEY,
    Name varchar(100),
    Description varchar(500),
    Price decimal(10,2),
    CategoryID int
);

Now let’s insert a few sample rows to fill your Products table with 8 entries.

INSERT INTO Products(ProductID, Name, Description, Price, CategoryID)
VALUES (1, 'Product 1', 'This is a description for product 1', 23.99, 10);

INSERT INTO Products(ProductID, Name, Description, Price, CategoryID)
VALUES (2, 'Product 2', 'This is a description for product 2', 45.50, 10);

INSERT INTO Products(ProductID, Name, Description, Price, CategoryID)
VALUES (3, 'Product 3', 'This is a description for product 3', 29.99, 20);

INSERT INTO Products(ProductID, Name, Description, Price, CategoryID)
VALUES (4, 'Product 4', 'This is a description for product 4', 33.00, 20);

INSERT INTO Products(ProductID, Name, Description, Price, CategoryID)
VALUES (5, 'Product 5', 'This is a description for product 5', 16.50, 30);

INSERT INTO Products(ProductID, Name, Description, Price, CategoryID)
VALUES (6, 'Product 6', 'This is a description for product 6', 20.00, 30);

INSERT INTO Products(ProductID, Name, Description, Price, CategoryID)
VALUES (7, 'Product 7', 'This is a description for product 7', 19.99, 40);

INSERT INTO Products(ProductID, Name, Description, Price, CategoryID)
VALUES (8, 'Product 8', 'This is a description for product 8', 24.99, 40);

These statements will create 8 products, each with a unique ID and having 5 columns:

Product ID
name
description
price
and associated category ID.

2. Query XML Data with SQL Server

T-SQL provides the FOR XML clause to return query results as XML. This can be used in various modes: RAW, AUTO, EXPLICIT, and PATH. For instance, to get all products in XML format:

SELECT ProductID, Name, Description, Price, CategoryID
FROM Products
FOR XML AUTO;

3. XML Data Modification in T-SQL

You can store XML data in a column by using the xml data type. For instance:

CREATE TABLE ProductDetails(
    ProductID int PRIMARY KEY,
    Details xml
);

To insert XML data in a SQL Server column, use the following script:

INSERT INTO ProductDetails(ProductID, Details)
VALUES (1, '<details><color>Red</color><size>M</size><weight>1.2</weight></details>')

4. Script example to query XML Columns

XML columns can be queried using the .value, .query, and .exist methods provided by T-SQL. For instance, to get the color of a product:

SELECT ProductID, 
       Details.value('(/details/color)[1]', 'varchar(50)') as Color
FROM ProductDetails

5. Modifying XML Columns

XML data can be modified using the .modify method. As an example, to update the color of a product, using the ProductID key, use and adapt a T-SQL script like this one:

UPDATE ProductDetails
SET Details.modify('replace value of (/details/color/text())[1] with "Blue"')
WHERE ProductID = 1

6. Updating XML data in a T-SQL Table

Consider that we have an XML column in the Products table to store additional details about the products. Let’s add this column:

ALTER TABLE Products
ADD ProductDetails xml;

We could store product specifications or other details in XML standard format. Let’s say we want to update the XML details for ProductID 1:

UPDATE Products
SET ProductDetails = '
<details>
    <color>Red</color>
    <size>Medium</size>
    <weight>1.2kg</weight>
</details>'
WHERE ProductID = 1;

With this, we can store structured XML data directly in the table and update it as needed.

7. Querying data in a T-SQL XML Table

Now that we have some XML data in our table, we may want to query this data. We can use the .value XML method in T-SQL to do this:

SELECT ProductID, 
       Name, 
       Price, 
       ProductDetails.value('(/details/color)[1]', 'varchar(50)') as Color, 
       ProductDetails.value('(/details/size)[1]', 'varchar(50)') as Size, 
       ProductDetails.value('(/details/weight)[1]', 'varchar(50)') as Weight
FROM Products
WHERE ProductID = 1;

This will return a row with the ProductID, Name, Price, and the values of the color, size, and weight XML nodes. Remember that working with XML data in SQL can be tricky, but it can also provide a lot of flexibility when you need to store structured data that doesn’t fit neatly into the SQL table structure. And of course when you need a flexible and changing data structure.

Conclusion on XML data in SQL Server

That’s it for how to use XML data type in SQL Server. This short tutorial has introduced the integration and use of XML data type with SQL Server data handling is a powerful feature in T-SQL, making it easier to exchange data between different platforms and systems. Practicing these concepts with more complex examples will help you become proficient in using XML with T-SQL. To go further, let’s check our other XML tutorial with a workaround to avoid the implicit XML conversion error.