Getting XML from MSSQL Server

21 Aug 2006 . . Comments

#coldfusion #XML #AJAX #MSSQL

I’ve been playing with all the AJAX stuff that’s been coming out lately. I suppose that like a lot of folks, I was creating a query, then having a generic function that created the XML in a proxy file for the JavaScript (Ray Camden has a really nice function for transforming a query to XML).

Last week I was doing some research to find a way to do some XML searching and stumbled upon the FOR XML statement. I knew that most RDBMSs were capable of dealing with XML record sets, but it’s been years since I’ve even looked at any of the XML stuff for MSSQL.

The FOR XML statement returns a query result and transforms rows into XML elements. There are three arguments that this can take:

  • RAW: Transforms each row into an element with a generic identifier () as the element tag.
  • AUTO: Returns the results in a simple nested XML tree
  • EXPLICIT: Allows you to define the XML tree returned