When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
   xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
   xmlns:rs='urn:schemas-microsoft-com:rowset'
   xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
   <s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
      <s:AttributeType name='FAQID' rs:number='1'>
         <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/>
      </s:AttributeType>
      <s:AttributeType name='Description' rs:number='2' rs:writeunknown='true'>
         <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255' rs:maybenull='false'/>
      </s:AttributeType>
      <s:AttributeType name='ViewCount' rs:number='3' rs:writeunknown='true'>
      ...
   </s:ElementType>
</s:Schema>
<rs:data>
   <z:row FAQID='106'
       Description='How can I find out if a record...'
       ViewCount='18201'
       FAQCategoryID='12'
       SubmittedByName='Steve Cimino'
       SubmittedByEmail='steve_cimino@hotmail.com'
       DateEntered='2000-12-14T16:59:11.780000000'
       CatName='Databases, Queries'/>
   <z:row FAQID='144'
       Description='Where can I host my ...'
       ViewCount='16472'
       FAQCategoryID='20'
       SubmittedByName='Scott Mitchell'
       SubmittedByEmail='mitchell@4guysfromrolla.com'
       DateEntered='2001-03-20T02:53:45.013000000'
       CatName='Getting Started'/>
   ...
</rs:data>
</xml>

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.



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article