Working with XML data in SQL Server

The XML data type and some associated functions were added back in SQL Server 2005. I'm looking forward to digging into this more deeply as I'm told Measurement Inc uses it extensively in their systems. Today I'll be writing some simple scripts to test out some of the functionality.

You can retrieve the results of a query directly into XML using the FOR XML statement. There are a few different modes that format the returned data differently. I tried a number of them below:

The results window displays links to the full XML that was returned with each query.

The full XML returned with the PATH mode.

This is cool stuff, but what if we want to store this XML natively in a table? Store it in an XML column like so:

Statement 1 creates a table with a single XML column. A variable is declared with the XML data type in statement 2. The third statement saves the information from two tables in that variable. Statement 4 inserts a row into the table using the variable and the final query returns the column without using the FOR XML clause. The data is stored in the XML format, so the results look just like the SELECT statement from the first script in this post.