To read the article online, visit http://www.4GuysFromRolla.com/webtech/060601-1.shtml

Creating a Function to Stream a Recordset to XML

By Scott Mitchell


For More Information on XML...
This article uses the Microsoft XMLDOM object to create an XML document from a Recordset. If you are not familiar with XML, or need to brush up on your XML skills, be sure to check out the many fine article in the XML Article Index. Also, if you are relatively new to XML, XML101.com is a great place to get started learning!

AN UPDATE IS AVAILABLE
While the code in this article will work fine, David O'Neill has written an article that illustrates how to tweak the function examined in this article for better performance. Once you've read this article and understand the concepts, take a moment to check out Increasing ConvertRStoXML() Performance!

ANOTHER UPDATE!
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.

Introduction:
Did you know that the ADO Recordset contains a Save method that can save the contents of a Recordset to an XML file? In fact, in an upcoming article on 4Guys this method will be discussed. One disadvantage of using this approach, though, is that the XML produced by the Recordset is anything but readable. Plus, there is a ton of information there, describing gobs of data for each column in the Recordset (its data type, precision, NULLability, maxlength, etc.). Additionally, when saving a Recordset to XML, the XML node names are archaic: each row's node name is z:row; the parent node is named rs:data.

In this article we are going to examine how to create a custom function that will take the contents of a Recordset and return a much friendly-on-the-eyes XML version. Once the ASP page calls this function and has, as a string, the XML structure, it can either save it to disk or write out the contents via Response.Write. The latter approach would be a useful way for sharing a site's data with other Web sites. For example, in the live demo for this article, we will look at sharing the 10 most popular FAQs from ASPFAQs.com as XML data. In a future article we will examine various ways for another Web site to consume and display this information.

Calling the Function
Ideally, our function to convert a Recordset into a human-friendly XML formatted file would only require one parameter, the Recordset object to convert, and return a string containing the XML. However, the custom function I created expects three parameters: objRS, strTopLevelNodeName, and strRowNodeName:

xmlString = ConvertRStoXML(objRS, strTopLevelNodeName, strRowNodeName)

The strTopLevelNodeName parameter specifies the name to be used for the root node element, while the strRowNodeName parameter specifies the name for each row-level node. If this sounds a little confusing now, don't worry, an example will help clear things up. Imagine that we had a Recordset containing product information. This Recordset might have the following contents:

ProductIDProductName
1Monitor
2Mouse
4Keyboard
6Scanner
. . .

When calling the ConvertRStoXML and passing in this Recordset, the XML we'll get back will look like:

<strTopLevelNodeName>
  <strRowNodeName>
    <field name="ProductID">
      <value>1</value>
    </field>
    <field name="ProductName">
      <value>Monitor</value>
    </field>
  </strRowNodeName>
  <strRowNodeName>
    <field name="ProductID">
      <value>2</value>
    </field>
    <field name="ProductName">
      <value>Mouse</value>
    </field>
  </strRowNodeName>
  ...
</strTopLevelNodeName>

Note that for each row in the Recordset there will be a strRowNodeName node. (Hopefully it is clear, now, that the string values you pass in to the ConvertRStoXML function determine the name of the top-level node (strTopLevelNodeName) and the node name for each row (strRowNodeName). For the above example I would recommend a strTopLevelNodeName of products and a strRowNodeName of product, which would give the XML:

<products>
  <product>
    <field name="ProductID">
      <value>1</value>
    </field>
    <field name="ProductName">
      <value>Monitor</value>
    </field>
  </product>
  <product>
    <field name="ProductID">
      <value>2</value>
    </field>
    <field name="ProductName">
      <value>Mouse</value>
    </field>
  </product>
  ...
</products>

Now that we've looked at how to call the ConvertRStoXML function, we need to examine the code for the function. We'll tackle this in Part 2 of this article.

  • Read Part 2!


  • Article Information
    Article Title: Creating a Function to Stream a Recordset to XML
    Article Author: Scott Mitchell
    Published Date: Wednesday, June 06, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/060601-1.shtml


    Copyright 2014 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers