4GuysFromRolla.com - Revisiting Converting a Recordset to XML

Windows Systems Administrator
Jupitermedia
US-CT-Darien

Published: Wednesday, April 14, 2004

Revisiting Converting a Recordset to XML
By Carlos Baptiste

Introduction
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 ConvertRStoXML() Performance and, in Simplifying ConvertRStoXML() and Transforming With XSL, looked at how to simplify the entire process by using an XSL stylesheet.

- continued -

David O'Neill's approach used the ADO Recordset's GetRows() 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 Recordset's 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 Save() method:

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 supports the 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:

dim objXML Set objXML = Server.CreateObject("MSXML2.DOMDocument") 'LOAD DATA dim objRS : set objRS = m_Connection.Execute(m_sSQL) 'TRANSFER DATA FROM RS TO XML With objRS Call .Save(objXML, 1) '1 - indicates to save Recordset as XML Call .Close() End With 'CLEAN UP RS Set objRS = nothing

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.

'/// Specify the connection string dim sConnectionString sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & Server.MapPath("northwind.mdb") & ";" '/// Open the connection to the database Dim oConn set oConn = Server.CreateObject("ADODB.Connection") oConn.Open sConnectionString dim oDB, myXML set oDB = new CDatabaseHelper With oDB '/// Specify the connection to use .Connection = oConn '===< ExecuteDataset Demo >=== '/// Specify the SQL query sSQL = "SELECT EmployeeID,FirstName, LastName FROM Employees" .CommandText = sSQL '/// Specify the name of each row-level element .RowLabel = "Employee" '/// Call ExecuteDataset - returns an DOMDocument Set myXML = .ExecuteDataset() End With '/// CLEAN UP oConn.close set oConn = nothing set oDB = nothing '/// OUTPUT XML Response.ContentType="text/xml" Response.Write myXML.xml Set myXML = nothing

The example above would emit information about the authors in the database. The following shows a snippet of the outputted XML:

<rowset> <header currentPage="1" pageCount="1" recordCount="9" pageSize="9" /> <Employee> <EmployeeID>1</EmployeeID> <FirstName>Nancy</FirstName> <LastName>Davolio</LastName> </Employee> <Employee> <EmployeeID>2</EmployeeID> <FirstName>Andrew</FirstName> <LastName>Fuller</LastName> </Employee> <Employee> <EmployeeID>3</EmployeeID> <FirstName>Janet</FirstName> <LastName>Leverling</LastName> </Employee> <Employee> <EmployeeID>4</EmployeeID> <FirstName>Margaret</FirstName> <LastName>Peacock</LastName> </Employee> ... </rowset>

Working with Shaped Recordsets
The 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 relationships.

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. Each <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:

<rowset> <header currentPage="1" pageCount="1" recordCount="9" pageSize="10" /> <Employee> <EmployeeID>1</EmployeeID> <FirstName>Nancy</FirstName> <LastName>Davolio</LastName> <Orders> <OrderID>10258</OrderID> <EmployeeID>1</EmployeeID> <OrderDate>1994-08-17 00:00:00</OrderDate> <RequiredDate>1994-09-14 00:00:00</RequiredDate> <ShippedDate>1994-08-23 00:00:00</ShippedDate> </Orders> <Orders> <OrderID>10270</OrderID> <EmployeeID>1</EmployeeID> <OrderDate>1994-09-01 00:00:00</OrderDate> <RequiredDate>1994-09-29 00:00:00</RequiredDate> <ShippedDate>1994-09-02 00:00:00</ShippedDate> </Orders> </Employee> <Employee> <EmployeeID>2</EmployeeID> <FirstName>Scott</FirstName> <LastName>Mitchell</LastName> <Orders> <OrderID>10275</OrderID> <EmployeeID>2</EmployeeID> <OrderDate>1994-08-01 00:00:00</OrderDate> <RequiredDate>1994-09-01 00:00:00</RequiredDate> <ShippedDate>1994-08-23 00:00:00</ShippedDate> </Orders> </Employee> ... </rowset>

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.

Conclusion
In this article we examined how to use the MSXML components along with the ADO Recordset object's Save() 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.

Happy Programming!

By Carlos Baptiste

Attachments

Download the CDatabaseHelper class, 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.