SQL Server Table to XML Variable

 in SQL Server  tagged xml by

This post is about working with XML in SQL Server. We are going to convert data in a simple table to XML. Here we illustrate ow to go from a table to xml. Since you will probably be working towards elements instead of attributes, scroll down the Path section. If you are using a stored procedure, you will be using XML variables. Also, chances are your XML will require headers and/or trailers and this data will be in another table or tables. Scroll down to the bottom of this post for suggestions on how to handle that.

We can run a simple SELECT query against a table to output an XML document in the Results pane. This output will be in the XML data type. Here is our results from a simple SELECT of the dbo.Authors table.

You will probably be using FOR XML PATH more than the others, however there are three ways to SELECT into an XML data type:

  • FOR XML AUTO – attributes
  • FOR XML RAW – attributes with
  • FOR XML PATH – elements

AUTO

When we run this query (listed below) we get XML output in the Results pane. The screenshot is below. We get one row for each row in the table. The table name became the tag name. Each column became an attribute. 

SELECT [FirstName]
      ,[LastName]
  FROM [myDatabase].[dbo].[Authors]
  FOR XML AUTO

When we click on the link in the above screen shot we get the following. 

<myDatabase.dbo.Authors FirstName="John" LastName="Smith" />
<myDatabase.dbo.Authors FirstName="Sally" LastName="Jackson" />

RAW

When we run the following query using FOR XML RAW instead of FOR XML AUTO we get the following output after we click on the link. Notice that each tag name is just row instead of the table name and each column became an attribute. 

<row FirstName="John" LastName="Smith" />
<row FirstName="Sally" LastName="Jackson" />

PATH

When we run the query with FOR XML PATH we get the following output when we click on the link. Now the XML is formatted with elements instead of attributes. 

<row>
  <FirstName>John</FirstName>
  <LastName>Smith</LastName>
</row>
<row>
  <FirstName>Sally</FirstName>
  <LastName>Jackson</LastName>
</row>

Specifying the ROOT

Instead of just using FOR XML PATH, we can specify the ROOT. 

SELECT [FirstName]
      ,[LastName]
  FROM [myDatabase].[dbo].[Authors]
  FOR XML PATH ('Author'), ROOT ('Authors');

Our output in SSMS Results pane is a text string like this if we are in Results to Text mode. In the Results to Grid mode we get a link that we can click on to see the XML. 

<Authors><Author><FirstName>John</FirstName><LastName>Smith</LastName></Author><Author><FirstName>Sally</FirstName><LastName>Jackson</LastName></Author></Authors>

If we format it to make it more readable we get this. 

<Authors>
	<Author>
		<FirstName>John</FirstName>
		<LastName>Smith</LastName>
	</Author>
	<Author>
		<FirstName>Sally</FirstName>
		<LastName>Jackson</LastName>
	</Author>
</Authors>

We now have the root element and the individual row elements.

Adding Tags

We have control over how we add tags and name tags in our xml document. We can go from a table to an xml variable. Consider our Books example. Below is a screenshot of the contents of the Books table in SSMS’s results pane. H=In this examle we have added the description tag that surrounds the three columns in the table.

The objective is to get this into an xml variable. Below is our code to do that. 

DECLARE @xml XML;
SET @xml = (
SELECT [BookTitle] as "title"
      ,[ISBN] as "description/isbn"
      ,[Authors] as "description/authors"
      ,[Quantity] as "description/quantity"
      ,[UnitPrice] as "unitprice"
  FROM [XMLbookstore].[dbo].[Books]
  FOR XML PATH('Books'), ROOT ('Book'));

SELECT @xml;

The output of this query is shown below. 

<Book>
  <Books>
    <title>My Book</title>
    <description>
      <isbn>123</isbn>
      <authors>Jack</authors>
      <quantity>2</quantity>
    </description>
    <unitprice>29.9900</unitprice>
  </Books>
  <Books>
    <title>Big Book</title>
    <description>
      <isbn>654</isbn>
      <authors>Sally</authors>
      <quantity>5</quantity>
    </description>
    <unitprice>19.0000</unitprice>
  </Books>
</Book>

If you don’t need the root element outputted, just change the FOR XML PATH line to the following. 

FOR XML PATH('Books'));

The surrounding tags can be whatever you want. Just change the last line to something like the following and replace “whatever” with what you need. 

FOR XML PATH('whatever'));

Multiple Tables – More Complexity

If your XML has a header, and it probably will, you will need to build it up more gradually. How do you do that? If you are using a stored procedure you can start with an XML variable (DECLARE @xmlALL XML;) with the root tags and perhaps a few other tags and then create another XML variable to store the results of your FOR XML PATH query that gets chunks of XML from the table. Then you can insert one XML chunk into your XML variable that you started with that contains the root (and perhaps other tags). How do you do that? You can use the modify() function. This post will not cover this in detail. The statement below gives you an idea of what the code looks like. 

SET @xmlALL.modify('insert sql:variable("@xmlHeader") as first into (/root)[1]')

For an example of this have a look at the post called SQL Server XQuery Insert xml Node.

We have a series of posts on XQuery starting with SQL Server XQuery Introduction.