Implementing XML in SQL Server

The XML data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the XML type and store XML instances in them.

Limitations

Note the following general limitations that apply to the XML data type:

The stored representation of XML data type instances cannot exceed 2 GB.
It cannot be used as a subtype of a sql_variant instance.
It does not support casting or converting to either text or ntext. Use varchar(max) or nvarchar(max) instead.
It cannot be compared or sorted. This means an XML data type cannot be used in a GROUP BY statement.
It cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.
It cannot be used as a key column in an index. However, it can be included as data in a clustered index or explicitly added to a nonclustered index using the INCLUDE keyword when the nonclustered index is created.

Demo

This demo is to be considered as a continuation of my previous article:

Introduction to Merge Statement in SQL Server

Steps

1. Create 2 Tables, "[Source]" and "[Target]", and insert some dummy data in both using the following script:

CREATE Table [Source] (id int, name varchar(50))

CREATE Table [Target] (id int, name varchar(50), status varchar(10))

TRUNCATE TABLE [Source]

TRUNCATE TABLE [Target]

INSERT INTO [Source] VALUES (1, 'abc'), (2,'pqr' ), (3, 'xyz')

INSERT INTO [Target](id, name)VALUES (1, 'abc'), (2,'sdfdf'), (4, 'abc')

2. Insert the data from the "[Target]" table to a temp table, "tempTarget2" as in the following:

SELECT id, name, [status]

INTO tempTarget2

FROM [Target]

3. Fetch the newly inserted temp table data using XML as follows:

SELECT id AS "@ID", name AS "@Name", status AS "@Status" FROM tempTarget2

--WHERE name like 'a%'

/*Here We can use any predicate like WHERE, ORDER BY etc. */

FOR XML PATH('product'), ROOT('products');

Here, we mapped the id as the ID parameter via id AS "@ID", similarly other columns are mapped in the child node's parameters query that will return the XML path to be mapped as nodes as "product" having the root node "product" as shown below:

inserted temp table data using XML

Remember: XML is case sensitive!

This query will return XML in the temp column header as in the following:

XML in table column

4. Copy the queried data and declare an XML variable like :

DECLARE @xml XML =N'<products>

</products>'

5. Querying through XML data syntax:

SELECT

xt.xc.value('@ID', 'int') AS id,

xt.xc.value('@Name', 'varchar(50)') AS name,

ISNULL(xt.xc.value('@Status', 'varchar(10)'), '') AS [status],

ISNULL(xt.xc.value('@Delete', 'bit'), 0) AS DoDelete

FROM @xml.nodes ('/products/product') AS xt(xc)

Explanation

Here we need to remember that XML has a root node and child nodes that have parameters that act like columns of a table, in other words here we query from the previously declared XML data using .nodes() and the node structure like "/products/product" must be used that must be exactly the same case as in the declared variable.

@xml.nodes('/products/product') AS xt(xc)

The returned data is selected using the .value() keyword as shown above and mapping each parameter to a unique column header and its datatype. For for example we mapped ID as the id column and its datatype is int as in the following:

xt.xc.value('@ID', 'int') AS id

For some column we checked for a NULL value and assigned a default value using the built-in function ISNULL() and if null then we assigned empty ('') and 0 bit as in the following:

ISNULL(xt.xc.value('@Status', 'varchar(10)'),'') AS [status],

ISNULL(xt.xc.value('@Delete', 'bit'), 0) AS DoDelete

6. For generalization, we can use the fetched data to a Common Table Expression (CTE) that is nothing more than a temporary table container as in the following:

DECLARE @xml XML =N'<products>

</products>';

/*Remember it need to have semicolon to start WITH CTE on next statement*/

WITH src AS(

SELECT

xt.xc.value('@ID', 'int') AS id,

xt.xc.value('@Name', 'varchar(50)') AS name,

ISNULL(xt.xc.value('@Status', 'varchar(10)'), '') AS status,

ISNULL(xt.xc.value('@Delete', 'bit'), 0) AS DoDelete

FROM @xml.nodes('/products/product') AS xt(xc)

)
SELECT * FROM src;

The final output will be as shown below:

Common Table Expression

7. Using a MERGE statement on the XML data type:

To understand how a MERGE works read my previous blog:

Introduction to Merge Statement in SQL Server

MERGE INTO [dbo].[tempTarget2] AS dest

USING src on dest.id = src.id

WHEN NOT MATCHED THEN INSERT (id, name, [status]) VALUES(src.id, src.Name, 'INSERTXML')

WHEN MATCHED AND src.DoDelete = 0 THEN UPDATE SET dest.name=src.name, dest.[status]='UPDATEXML'

WHEN MATCHED AND src.DoDelete = 1 THEN DELETE;

Explanation

Just as in a simple MERGE statement, we use the CTE src table as the source and merge it with the target table tempTarget2 based on the matched and unmatched and with the node value of the source table.

Finally, select the temp table content that is merged based on our conditions:

MERGE statement on XML