Converting MSSQL XML to a native ColdFusion Query
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:
*
from
Employee
order by
LastName
for xml raw
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:
* Converts a MSSQL XML packet to a native CF Query object
* Just add the following string to the end of your SQL statement:
*
* FOR XML RAW, ELEMENTS XSINIL, XMLSCHEMA, ROOT('rows')
*
* @param xml XML string. (Required)
* @return Returns a query object.
* @author Dan G. Switzer, II (dswitzer@pengoworks.com)
*
* @version 1.0, Jan 10, 2008
*
*/
function mssqlXmlToQuery(xml){
// parse the xml string into an xml object
var oXml = xmlParse(xml);
// get the column data
var aElement = xmlSearch(oXml, "//*[local-name()='element']");
// create arrays to track columns
var aColumnNames = arrayNew(1);
var aColumnTypes = arrayNew(1);
var stColumnMappings = structNew();
// track current column type
var sColumnType = "";
// define loop counters
var i = 0;
var j = 0;
// query object which is returned
var qReturn = "";
// temp variables
var sCellValue = "";
for( i=2; i lte arrayLen(aElement); i=i+1 ){
// add the name of the column to the array
arrayAppend(aColumnNames, aElement[i].xmlAttributes.name);
// if the datatype exists as an attribute, get it
if( structKeyExists(aElement[i].xmlAttributes, "type") ){
sColumnType = aElement[i].xmlAttributes.type;
// otherwise get it from it's child elements
} else {
sColumnType = aElement[i].xmlChildren[1].xmlChildren[1].xmlAttributes.base;
}
// strip out the sql info ("sqltypes:")
sColumnType = listGetAt(sColumnType, 2, ":");
// convert sql datatype to cf-query datatypes
if( sColumnType contains "int" ){
sColumnType = "integer";
} else if ( listFind("float,real,numeric", sColumnType) ){
sColumnType = "double";
} else if ( sColumnType contains "datetime" ){
sColumnType = "date";
} else if ( sColumnType eq "timestamp" ){
sColumnType = "time";
} else if ( sColumnType contains "money" ){
sColumnType = "decimal";
} else {
sColumnType = "varchar";
}
// add the column type to the array
arrayAppend(aColumnTypes, sColumnType);
// create a mapping which maps the column name to it's datatype
stColumnMappings[aElement[i].xmlAttributes.name] = sColumnType;
}
// create the CF query object
qReturn = queryNew(arrayToList(aColumnNames), arrayToList(aColumnTypes));
// get all the rows in the query
aRow = xmlSearch(oXml, "//*[local-name()='row']");
// convert each row into it's native CF row
for( i=1; i lte arrayLen(aRow); i=i+1 ){
// add a new row
queryAddRow(qReturn);
for( j=1; j lte arrayLen(aRow[i].xmlChildren); j=j+1 ){
// get the value for the current row
sCellValue = aRow[i].xmlChildren[j].xmlText;
// do any necessary conversions
if( stColumnMappings[aRow[i].xmlChildren[j].xmlName] eq "date" ){
// convert the ISO 8601 date/time into an ODBC date/time
sCellValue = createODBCDateTime( left(sCellValue, 10) & " " & mid(sCellValue, 12, 8) );
}
// add the data to the query
querySetCell(qReturn, aRow[i].xmlChildren[j].xmlName, sCellValue);
}
}
// return the cf query
return qReturn;
}
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.
2300 Views
Send
Print
Comments (0)