Every now and again I have the need to test some code against against some live data. The reasons vary from simply trying to recreate a bug, testing a piece of code for performance or for testing a UI widget.
In the past the quick and dirty way I've done was to move a template into production that would perform the query and then convert the query object to WDDX. This would allow me to create a "copy" of the live data that I could port to development. The problem with this method is it makes you move temporary code to a production environment that you then have to remove.
Today I ran into a situation where I again needed to test some code against some actual production data. I'm doing some UI work where I'm trying to improve navigating a large hierarchical tree of data. While I've got a pretty decent size test package to work with, nothing beats testing the code against actual data our users would be using.
I was originally going to use the WDDX method, but I knew I'd be needing to do this basic thing several times in the near future and I've never really liked having to move temp files into production.
This got me thinking about MSSQL's ability to export a query object as XML. If I could simple run a query against the database and save the results as an XML file, these would make grabbing snapshots of live queries very easy.
Converting a query to XML in MSSQL 2000 (and above) is very easy. You just add the FOR XML RAW command to end of your SQL statement. The following is a very basic SQL example:
There are a number of directives you can use for formatting of the XML so the first thing I had to do was figure out which directives I wanted to use to format the XML so it would be easy to parse with ColdFusion. I ended up deciding to go with the following:
The FOR XML RAW gives me a raw dump of the day dumped out in <row> elements. The ELEMENTS XSINIL directive makes sure that even columns with null data are generated in the output. The XMLSCHEMA directive gives me detailed information on each column (so I can map the column data to a specific query column datatype.) Finally the ROOT('rows') directive makes sure that all the output is placed between a open and close <rows> element.
The next step was to create a ColdFusion UDF which would parse the XML that SQL Server generates and convert it to a native CF query object. Here's what I came up with:
To use the UDF, just SQL directives FOR XML RAW, ELEMENTS XSINIL, XMLSCHEMA, ROOT('rows') to the end of any SQL command. You can then cut-n-paste the resulting XML into a CF variable or save it as an XML file (which you can then read in using CFFILE.)
I tried to make sure that I captured most use cases, but I'm sure the UDF may need a little bit of tweaking if you're using some less commonly used datatypes. Also, I haven't even attempt to try the code against a BLOB/CLOB as it's outside of my normal use case for this function.
Anyway, let me know if you find this function useful. If you find any bugs, make sure to post the fix as a comment. I'll keep the UDF source up-to-date with any of the bug fixes or enhancements.