Output SQL Data to XML easily

So today I needed to output data to XML at my job and rather than going the traditional way of creating a ColdFusion file to query and output the data I decided to see if I could just do it in MSSQL. Well, appears there is by using the "for XML" clause. There is a lot to learn from it and the docs are available in the following links.

• SQL Server 2000 XML
• What's New in FOR XML for SQL 2005
• What's New in FOR XML for SQL 2008 White Paper
  I think this white-paper explains the difference between SQL versions the best.

What I will do here is just explain some of the basics so you can go ahead and do as you please from here on. For my examples I will use the AdventureWorks default database but you can easily modify the SQL I have to your own and play with the results.

There are several ways to get XML output based on the XML Mode used, the following are available.

• RAW
  The RAW mode takes the query result and transforms each row in the result set into an XML element with a generic identifier row as the element tag and the columns in the SELECT as attributes.
• AUTO The AUTO mode returns query results in a simple, nested XML tree.
• EXPLICIT (I DO NOT DEMO THIS ONE)
  In EXPLICIT mode, you can explicitly define the shape of the resulting XML tree. Using this mode requires that the queries be written in a specific way, so that additional information about the desired nesting is specified explicitly as part of the query. The reason I don't demo this one is because of the new PATH mode in SQL 2005+, if you are still in 2000 and need to write complex XML docs then I suggest you look up this mode.
• PATH (SQL 2005+)
  A simple way to generate complex XML documents rather than explicit.

The examples are as follows

• Using for XML AUTO
• Using for XML PATH
• Using for XML PATH('path'),ROOT('root')
• Using for XML PATH('path'),ROOT('root') and making the contactID column an attribute
• Using for XML RAW('path'),ROOT('root')

---

Using for XML AUTO Returns this

Back to top

---

Using for XML PATH Returns this

Back to top

---

Using for XML PATH('path'),ROOT('root')
Here we add a root node and a cutom name for each row by defining the path value. Returns this

Back to top

---

Using for XML PATH('path'),ROOT('root') and making the contactID column an attribute
Here we add a root node and a custom name for each row by defining the path value. Returns this

Back to top

---

Using for XML RAW('path'),ROOT('root'), ELEMENTS XSINIL
This is the same as PATH it just returns the parent node with the XML Namespace. The ELEMENTS directive provides an XSINIL option to map NULL values to an element with an attribute xsi:nil="true". Returns this

Back to top

In conclusion

When working with this data you can either render your results to text and/or grid. I prefered grid because the results are essentially a link that opens the xml document which then you can either copy and paste or save to your hard drive.

Some books to consider ....

Beginning SQL Server 2008 for Developers: From Novice to Professional
SQL Server 2008, Developer Edition
Murach's SQL Server 2008 for Developers (Murach: Training & Reference)