Immediately convert a Recordset into XML

by Phillip Perkins | Jun 12, 2003 7:00:00 AM

Tags: Phillip Perkins

Takeaway: XML can help developers quickly and cleanly transfer data between Web pages and databases--provided they can efficiently generate XML data. Find out how to use ASP to convert a Recordset into clean XML without resorting to high-overhead database tools.

This article originally appeared as a Web Development e-newsletter.

More and more Web application developers are jumping on the XML bandwagon. But a great number of them forge ahead without mastering the essential XML skills. One of the most common signs of this lack of expertise is the way developers create their XML from a database.

Using database tools for creating XML is preferred, but they're not always available. Microsoft SQL 2000 and Oracle are examples of database servers that allow you to return XML data from a query. However, if you have to create your own XML data using ASP, you'll use a Recordset to produce a large XML string. Although this is the first approach to creating an XML string, it's usually not the fastest.

A more desirable approach would be to use a tool that allows you to immediately convert a Recordset into XML. If you have Microsoft Data Access Components (MDAC) 2.6 or above and Microsoft XML (MSXML) 2.6 or above installed, you can create this XML quickly.

Let's say that you have an active connection to a database, and you're running a simple query to grab some data:
 
Set rs.ActiveConnection = conn 'our active connection.
rs.Open "SELECT * FROM DB.USERS"
 

You've grabbed all the fields from the USERS table, which include user_id, fname, lname, and zip. Instead of creating a While statement to loop through each record in the Recordset, it's much easier to use the Save method of the Recordset to save this information directly into a DOMDocument:
 
Dim oDOM
Set oDOM = Server.CreateObject("MSXML2.DOMDocument")
oDOM.async = False
rs.Save oDOM, 1 'adPersistXML
 

This opens the IStream interface on the DOMDocument and writes the XML data directly to the DOMDocument in XML format. This XML data contains some generally useless namespace information, so if you want just pure XML, you'll have to add this XML to a client-side XML data island and pass it through an XSL transformation. Using XSL, you can transform the XML, which contains schema information for row and data definitions, into XML that contains nothing more than the data you're interested in. I've created a generic XSL style sheet for converting any simple Recordset XML into plain XML:
 
<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:z="#RowsetSchema">
    <s:Schema id="RowsetSchema"/>
<xsl:output method="xml" omit-xml-declaration="yes" />
<xsl:template match="/">
    <xsl:apply-templates select="//z:row"/>
</xsl:template>
 
<xsl:template match="z:row">
    <xsl:text disable-output-escaping="yes">&lt;row&gt;</xsl:text>
        <xsl:for-each select="@*">
            <xsl:text disable-output-escaping="yes">&lt;</xsl:text>
            <xsl:value-of select="name()"/>
            <xsl:text disable-output-escaping="yes">&gt;</xsl:text>
            <xsl:value-of select="."/>
            <xsl:text disable-output-escaping="yes">&lt;/</xsl:text>
            <xsl:value-of select="name()"/>
            <xsl:text disable-output-escaping="yes">&gt;</xsl:text>
        </xsl:for-each>
    <xsl:text disable-output-escaping="yes">&lt;/row&gt;</xsl:text>
</xsl:template>
</xsl:stylesheet>
 

The style sheet converts each z:row node into a more natural form. When the Recordset is saved as XML, each row is converted to a z:row node with a group of attributes for each field in a record. These field attribute values are set to the current value of the field.

Each attribute is converted into a <fieldname>fieldvalue</fieldname> format. To prevent namespace information from being appended to the new nodes, I use the <xsl:text> element to create the < and > symbols instead of using <xsl:element> to create new elements.

The attribute name is returned using the name() XPath function, and the value of the attribute is returned with ".". All of these new nodes are wrapped in a <row> element after conversion.

Here's the code to run the transformation:
 
Dim oXSL
Set oXSL = Server.CreateObject("MSXML2.DOMDocument")
oXSL.async = False
oXSL.load Server.MapPath("ADOGeneric.xsl") 'our XSL stylesheet
Response.Write "<XML id='xmlData' name='xmlData'><root>" & vbCrLf
Response.Write oXML.transformNode(oXSL)
Response.Write "</root></XML>"
 

The process should leave you with clean XML. For more information on XSLT, visit Microsoft's XSLT reference in the MSDN Library.

Print/View all Posts Comments on this article

How do you save the XML as a fileAgentASP  | 07/06/03

Use the Save methodkburns@...  | 07/07/03