Create XML Fragment in SQL Server

The XML datatype, first introduced in SQL Server 2005, can be very  handy when utilized properly. It is an industry standard, and with it, it is easy to import/export data across different servers, as well as different systems entirely. This tutorial will guide you through creating an XML fragment on the fly in SQL Server. Additional resources, as well as reference materials can be found at the bottom of this post. These instructions are valid for both SQL Server 2005 and SQL Server 2008.

First off, we need some sample data:

DECLARE @Students TABLE (
 FirstName nvarchar(50),
 LastName nvarchar(50),
 DisplayName nvarchar(100)
)
INSERT INTO @Students
SELECT 'Jim' as FirstName, 'Bob' as LastName, 'Jim Bob' as DisplayName
UNION
SELECT 'John', 'Doe', 'John Doe'
UNION
SELECT 'Jane', 'Doe', 'Jane Doe'
UNION
SELECT 'Yuri', 'Tao', 'Yuri Tao'

Now that we have our test data, we can attempt to create an XML string for each distinct row. Ultimately, we want our table to end up like the sample below:

XML

We could try our first option:

select * from @Students FOR XML RAW

However, this option returns an XML fragment for the entire dataset – not what we’re looking for here. We need an individual XML fragment for each row.

After searching around on the net and a few forums, I was finally able to get an answer:

SELECT *
FROM @Students s
CROSS APPLY
(
 SELECT
 (
 SELECT *
 FROM @Students t
 WHERE t.DisplayName = s.DisplayName
 FOR XML RAW
 ) x1
)x

The CROSS APPLY function basically creates a Cartesian product. In this case, it iterates over each row in our table and produces the desired XML result. However, if you were to run this query as-is on our sample data, you would notice that the XML output, while formatted as XML, isn’t of an XML datatype.

To fix this, simply convert the column, like so:

SELECT s.*, CONVERT(XML,x.x1) as ErrorData
FROM @Students s
CROSS APPLY
(
 SELECT
 (
 SELECT *
 FROM @Students t
 WHERE t.DisplayName = s.DisplayName
 FOR XML RAW
 ) x1
)x

That’s all there is to it! We now have an XML fragment for each row of data in our sample. As always, test the example for performance bottlenecks. I’ve utilized this 50,000 records and it returns in about 1 minute. Not too bad, given that the records are very wide (40-50 columns).

Later, I will write a post detailing querying and shredding XML data into a relational dataset – good stuff indeed!!

Additional Resources:

Leave a Reply