Output data as raw XML from MS SQL – a little known feature

In the modern world we live in with Web Services, RPC calls and the like, SQL server has had a lot of work on its hands trying to keep its followers from leaving to newer ways to store data such as document databases. Microsoft SQL server is like an old friend that developers have either come to love or hate – but like old friends, they may have been keeping some secrets. One of these hidden gems appears in the shape of native XML output support for queries.

Microsoft SQL server has had XML output support since the release of SQL Server 2000, so it may come as a surprise that this feature does not get brought up more often or used more frequently. You may have even heard of it before but never given it a try – put it in an assumed “crappy feature bloat” basket and moved on. You may even think I'm talking about the XML data type in SQL server 2005 onwards (I'm not). Whether you knew about the feature or simply needed reminding, lets take a look at how you use it, and some examples of when it will be most handy.

There are three different modes that SQL can output XML – RAW, AUTO, and EXPLICIT.

Simple example (using AUTO)

So you have a product table, or a user table – and all you want is a simple XML version of the table? just add the phrase FOR XML AUTO on the end of your query and you’re done!

Query:

SELECT * from Products
for XML auto

Output:

<Products ProductID="17" ProductName="Blue Bicycle" CategoryID="1" />
<Products ProductID="18" ProductName="Red Pen" CategoryID="2" />
<Products ProductID="19" ProductName="Orange Chair" CategoryID="2" />

Whether it has wrapping tags or not, you have to admin, this is pretty cool!

This gets even cooler if we use this command on a joined query:

Query:

SELECT * from Categories
INNER JOIN Products ON Products.CategoryID = Categories.ID 
for XML auto

Outputs the following XML

<Categories ID="1" CategoryName="Bikes">
    <Products ProductID="17" ProductName="Blue Bicycle" CategoryID="1" />
</Categories>
<Categories ID="2" CategoryName="Other Products">
    <Products ProductID="18" ProductName="Red Pen" CategoryID="2" />
    <Products ProductID="19" ProductName="Orange Chair" CategoryID="2" />
</Categories>

The beauty of this is that if you have an application that relies heavily on XML data, or uses a lot of XSLT’s over the top of XML data sources, you can get rid of any middle-man application tier and use SQL to output your XML directly to you application. You can then run XPATH etc to grab the data out directly from the XML.

RAW mode output

using the RAW SQL XML mode gives a row based XML output which can be handy if you just want to iterate through the data in a standard row by row fashion. I will use our second query so that you understand that even complicated data come out as rows – similar to how a normal dataset comes out. You must note though, that if your query returns columns that are the same name, SQL server will give you an error – I'm lucky in the sense that with my demo tables, Categories and Products, all column names are unique – this may not be the same in your project, and you will have to make sure you give each returning column a unique name.

Query:

SELECT * from Categories
INNER JOIN Products ON Products.CategoryID = Categories.ID 
for XML RAW

Output:

<row CategoryID="1" CategoryName="Bikes" ProductID="17" ProductName="Blue Bicycle"/>
<row CategoryID="2" CategoryName="Other Products" ProductID="18" ProductName="Red Pen"/>
<row CategoryID="2" CategoryName="Other Products" ProductID="19" ProductName="Orange Chair"/>

Explicitly controlling the output using EXPLICIT mode

The third mode that SQL server allows gives you pretty good control over what comes out – making it easy to modify your output to match most requirements you may have. You’ll notice that the query is a lot more complex simply because what i am trying to achieve is a bunch of rows that have Tag and Parent columns so that SQL knows to put them into the hierarchy that i am after – in this instance, placing product nodes inside category nodes.

Query:

--The Category Data
SELECT 1 as Tag,
NULL as Parent,
    c.ID as [Category!1!Id],
    c.CategoryName     as [Category!1!CategoryName],
    NULL        as[product!2!ProductID],
    NULL        as [product!2!ProductName]
 FROM Categories c

UNION ALL

-- The Product Data
SELECT 2, 1,
    c.ID,
    c.CategoryName,
    p.ProductID,
    p.ProductName
FROM Categories c, Products p

WHERE c.ID = p.CategoryID
ORDER BY [Category!1!CategoryName]

FOR XML EXPLICIT

Output:

<Category Id="1" CategoryName="Bikes">
    <product ProductID="17" ProductName="Blue Bicycle" />
</Category>
<Category Id="2" CategoryName="Other Products">
    <product ProductID="18" ProductName="Red Pen" />
    <product ProductID="19" ProductName="Orange Chair" />
</Category>

So in closing…

Using SQL to output XML isn’t for everyone, and I'm not recommending that you go replace your entire web service application tier with SQL that returns all your data as XML – in fact i have a very religious post on something similar coming up shortly. Having given you this caveat, you must admit that in some circumstances this feature is a great tool to have in your bat belt. It’s especially handy where you have multiple “client” applications accessing or serving out the same XML data, as your output is coming from one place, and to change the output you only have to change your logic in one place. I can imagine a number of solutions where having the ability to swap out a .Net web service for a Java, Ruby or Django web service without having to change any business logic quite a handy trick to pull.

66KNXFCE5SSS