Simplifying ConvertRStoXML() and Transforming With XSL

An Update is Available!
Carlos Baptiste has written an article titled Revisiting Converting a Recordset to XML that looks at using the MSXML DOMDocument object and XSL stylesheets to transform the Recordset's native XML output into a human-readable XML output with just a few lines of code.

Introduction
This article is an update to an article I wrote back in July on improving the performance of the ConvertRStoXML() function built by Scott Mitchell. That function converted the contents of a Recordset to an easy-to-read XML file. This article will only cover some updates and improvements to the ConvertRStoXML() function, so to get the whole story please read the first two articles: Creating a Function to Stream a Recordset to XML and Increasing ConvertRStoXML() Performance.

Over the last six months I have received much feedback on the Increasing ConvertRStoXML() Performance article. The initial function was intended to be more of an idea catalyst than production code, but it looks like people were intent on using it "out of the box" and ran into a couple problems. Well, I'd say it's about time I tightened a few of the nuts and bolts up and improve the overall performance!

Solving for NULLs
The main complaint I had from people was that the function really didn't like NULLs. In fact, it would oftentimes die whenever it would encounter a NULL. This is now fixed.

Making the XML More Readable by Reducing the Node Count
Charlie Tennessen wrote me to point out that we could really simplify our XML structure by not using separate nodes for the name then the value. For example, the ConvertRStoXML() function currently creates XML documents like:

<orders> <order> <field name="OrderID"> <value>5528</value> </field> <field name="CustomerID"> <value>1234</value> </field> <field name="EmployeeID"> <value>5678</value> </field> </order> <order> <field name="OrderID"> <value>5677</value> </field> <field name="CustomerID"> <value>2345</value> </field> <field name="EmployeeID"> <value>6789</value> </field> </order> </orders>

Note that in the above structure each row in the table gets an <order> node whereas each column gets a <field> node with its name attribute set to the name of the database column. Rather than using this approach, the XML file becomes much more readable if we simply name each column node the name of the column, and instead of using a <value> node for the value of the column, we could simply place it inside the column. The resulting XML would be like so:

<orders> <order> <OrderID>5528</OrderID> <CustomerID>1234</CustomerID> <EmployeeID>5678</EmployeeID> </order> <order> <OrderID>5677</OrderID> <CustomerID>2345</CustomerID> <EmployeeID>6789</EmployeeID> </order> </orders>

See, much simpler to read for a human and as you will see much simpler (and much faster!) to build for a script! The script now doesn't need to create all these <field> and <value> nodes, and can skip right to the chase.

The necessary alterations to the ConvertRStoXML() function are as follows. (The complete code is available for downloading at the bottom of the article.) First, create the template row on which all other rows will be based. This cuts down on object instantiation and therefore keeps things running quickly.

Set objXMLRow = objXMLDom.CreateElement(strRowNodeName) For Each Field in objRS.Fields Set objXMLField = objXMLDom.createElement(Field.Name) objXMLRow.appendChild objXMLField 'Copy the field to give it a new memory address '(copy all its children too) Set objXMLField = objXMLField.cloneNode(True) Next

Now, convert the Recordset to a multi-dimensional array and calculate its bounds since accessing indexes in an array is much faster than going after the fields in a Recordset object every time.

aryRows = objRS.getRows() intNumFields = UBound(aryRows) intNumRows = UBound(aryRows, 2)

Next, iterate through the array of data and build the rows.

For intCurRowIdx = 0 To intNumRows 'Retrieve all the Field nodes within the Row Set objXMLNodeList = objXMLRow.ChildNodes 'Add the data for the fields. 'We know there's only one child for each Field (the FieldValue node) 'so the FirstChild property will work fine For intCurFieldIdx = 0 To intNumFields Set objXMLField = objXMLNodeList.item(intCurFieldIdx) 'Set objXMLFieldValue = objXMLField.FirstChild 'xml doesn't do nulls If Not IsNull(aryRows(intCurFieldIdx, intCurRowIdx)) Then objXMLField.text = aryRows(intCurFieldIdx, intCurRowIdx) End If Next 'Build the populated Row objXMLRoot.appendChild objXMLRow 'Copy the current row to give it a new memory address (copy children too) Set objXMLRow = objXMLRow.CloneNode(True) Next

Pretty easy. Not quite a no-brainer but well worth it. This script runs many many times faster than even the improved ConvertXMLtoRS() script published in July, which was in turn many many times faster than the original. Is this Moore's Law at work? This and other questions such as "Why is the Windows defragmenter so exciting to watch?" may never be answered.

Now that we've looked at how to improve the performance of the ConvertRStoXML() function, let's turn our attention to improving the function by allowing for the XML to be converted to HTML via XSL. We'll examine how to accomplish this in Part 2.