dans.blog: 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:

select
    *
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:

FOR XML RAW, ELEMENTS XSINIL, XMLSCHEMA, ROOT('rows')

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.

Categories: HTML/ColdFusion, Source Code

Comments for this entry have been disabled.