Getting XML from MSSQL Server
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 (<row/>) as the element tag.
- AUTO: Returns the results in a simple nested XML tree
- EXPLICIT: Allows you to define the XML tree returned
So, I decided to give this a try on a tagging system for the databases the library subscribes to:
SELECT *
FROM links
FOR XML AUTO
</cfquery>
I know that we have 225 records in this particular table, and what I was expecting to happen was that MSSQL would return a recordset with one record. However, it returned back a resultset with 65 records with chunked XML in an arbitrary fieldname. So what I did was write a little function that concatenates all the info into a single string:
<cfargument name="query" required="true" type="query" hint="Query to convert to XML.">
<cfargument name="rootElement" required="false" type="string" default="root" />
<cfset var sb = createObject("java", "java.lang.StringBuffer") />
<cfset var fldName = arguments.query.ColumnList />
<cfset sb.init('<?xml version="1.0" ?>') />
<cfset sb.append("<#arguments.rootElement#>")>
<cfloop from="1" to="#arguments.query.recordCount#" index="i">
<cfset sb.append(arguments.query[fldName][i])/>
</cfloop>
<cfset sb.append("</#arguments.rootElement#>") />
<cfreturn sb.toString() />
</cffunction>
(I used the java.lang.StringBuffer for this, but if you're using Java 1.5, use the java.lang.StringBuilder class...it's much faster).
The big question is, "is this any faster?" Well, the answer is, it's quite a bit faster. While this is a bit quick-and-dirty, looping over the calls with CFTimer produced the following results:
| Using FOR XML | Using QueryToXML UDF |
| 31ms | 187ms |
| 32ms | 156ms |
| 31ms | 329ms |
| 47ms | 593ms |
| 31ms | 282ms |
| 31ms | 265ms |
| 31ms | 328ms |
| 32ms | 313ms |
| 31ms | 297ms |
| 344ms | 328ms |
For this run, we're looking at an average of just over 64ms using the FOR XML clause and just over a 300ms average using a generic query function (I used Ray's QueryToXML as the benchmark). I didn't see much of a performance increase using FOR XML RAW or FOR XML EXPLICIT. I also ran this on a much larger dataset (all entries for all the blogs here at the library...around 1500). The average using the FOR XML RAW clause was 484.4ms compared to 1362.5ms.
So, if you have large datasets that you need to convert to XML (at least for MSSQL), give the FOR XML clause a try. I haven't looked at MySQL or Oracle yet, but I suspect they have a similar way of dealing with XML record sets that are similarly as fast.
thanks
Very helpful, thanks!