Revisiting Converting a Recordset to XMLBy Carlos Baptiste
There have been several articles on 4Guys on retrieving XML from an ADO Recordset. Scott Mitchell's article Creating a Function to Stream a Recordset to XML examined how to use the MSXML COM components to convert a Recordset into an XML string. David O'Neill extended Scott's article in Increasing
and, in Simplifying
ConvertRStoXML() and Transforming With XSL, looked
at how to simplify the entire process by using an XSL stylesheet.
David O'Neill's approach used the ADO Recordset's
method to convert a Recordset into an array. Next, David iterated the contents of the array, programmatically constructing
the XML document. In this article I'll examine how to simplify this process even further by using the
Save() method to save the data to an MSXML DOMDocument object. Furthermore, we'll look at how
to use XSL to specify the XML structure for the Recordset's data.
Streaming XML to an MSXML DOMDocument Object
The ADO Recordset object has a
Save() method that can save the contents of a Recordset to a file in an XML
format. Unfortunately, the XML generated by saving an ADO Recordset is both ugly and difficult to use. The following
shows an abbreviated snippet of the XML generated by the ADO Recordset's
In addition to the ugly XML generated by the
Save() method, I've found that developers typically use
Save() to store the XML to the filesystem. Using the filesystem has its own set of problems associated
with it, such as permissions and file maintenance to conserve disk space. The approach I will suggest below works
strictly with streams and does not involve the filesystem. In addition, it goes further into features of the
MSXML parser that allows the generation of Recordset-based XML in fewer lines of code.
One of the lesser-known features of the Recordset is that the
Save() method is overloaded. This means
that in addition to providing a file path specifying a file to save the XML to, one could pass in any object that
IStream interface. We will not get into the details of interface implementation, just
understand that the XML DOMDocument object supports this interface. This allows us to do the following:
At this point we have transferred our data from the Recordset to the XML Document.
Transforming the XML Using a Stylesheet
While we have seen how to squirt a Recordset's data into an MSXML DOMDocument object with just a few lines of code, the XML structure in the DOMDocument object is that default, ugly ADO Recordset format we examined earlier. What we'd like to do is use an XSL stylesheet to transform the XML into a more human-readable format. This can be accomplished using the XML DOMDocument's
transformNodeToObject() method. This method accepts an XSL stylesheet as an XML
DOMDocument and a target XML DOMDocument. The target XML DOMDocument is loaded with the output of your transformation.
I have created an XSL stylesheet written specifically to translate the ADO specific Schema to a more recognizable element-based XML (attribute-based XML is also available but commented out in the XSL file).
I have implemented these features in a VBScript class called
CDatabaseHelper, which encapsulates
database calls cleanly. To retrieve XML from the database you can call the
ExecuteDataSet() method of this
class, or you can call the
Execute() method which accepts a return type argument.
(This XSL stylesheet and
CDatabaseHelper class are both available for download at the end of this article.)
The following code illustrates how to use the
CDatabaseHelper class to run a simple SQL query against
the Access Northwind database, returning a human-readable XML stream.
The example above would emit information about the authors in the database. The following shows a snippet of the outputted XML:
Working with Shaped Recordsets
CDatabaseHelper class can also be used to transform shaped Recordsets into XML.
(Shaped Recordsets have been discussed here on 4Guys previously; see Richard Chisholm's Converting
Hierarchical Recordsets into XML for more information.)
To handle shaped Recordsets, the XSL stylesheet calls itself recursively when there is a child element present.
To this end, this approach will support shaped Recordsets with parent-child-sibling and parent-child-grandchild
Included in the download is an ASP page that demonstrates working with shaped Recordsets. The example
RS2XML_Shaped.asp) displays a list of employees and the orders they've made.
<Employee> element contains within it a list of orders made by that employee. The following
snippet shows what the XML emitted by the
RS2XML_Shaped.asp looks like:
As this XML example shows, any orders made by an employee are presented as child elements of said employee. In closing, the XSL stylesheet approach makes it a breeze to convert parent/child relationships into appropriately formatted XML.
In this article we examined how to use the MSXML components along with the ADO Recordset object's
method to convert database data (even shaped data) into human-readable XML through the use of a stylesheet and a
few lines of code. Those still working with classic ASP will find this approach useful; for those who have moved onto
ASP.NET, though, the .NET Framework's DataSet class and assorted
System.Xml classes can accomplish the
same functionality in even fewer lines of code.
CDatabaseHelperclass, XSL stylesheet, and example code (in ZIP format)
About the Author
Carlos Baptiste is a freelance software architect and mentor who specializes in Classic ASP / COM application development. He has over five years of experience developing web-based applications. He is based in the Washington, DC/Northern Virginia Area and can be reached through his website at Audoris.com.