By David O'Neill
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.|
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
Over the last six months I have received much feedback on the Increasing
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
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:
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
node for the value of the column, we could simply place it inside the column. The resulting XML would
be like so:
See, much simpler to read for a human and as you will see much simpler (and much faster!) to build for
The script now doesn't need to create all these
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.
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.
Next, iterate through the array of data and build the rows.
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
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.