Wednesday, September 2, 2009

Simple Talk article about TSQL Select For XML clause

I had to do some work today creating a C# web service that would query a MS SQL database and return an XmlDocument with the results. After writing the query I tried loading the data into a DataTable and then using the WriteXML method to turn it into XML, but the layout wasn't exactly what I wanted and the use of a TableAdapter, StringWriter, XMLTextWriter, and XMLDocument seemed a bit excessive. I then remembered that MS SQL 2000, 2005, and 2008 support the For XML clause on Select statements. I had read an article from Simple Talk a few months ago about the different options and by adding "FOR XML RAW ('LogEntry'), ROOT ('LogEntries')" to the end of the query I was able to get the SQL server to format the data for me and then use the ExecuteXmlReader() method to create an XmlReader and load the data into an XmlDocument. The article is a great read for anyone that needs to work with XML data from an MS SQL server.

No comments:

Post a Comment

Blog.TheG2.Net - Your guide to life in the Internet age.