Published: Wednesday, June 06, 2001
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:
ProductID | ProductName |
|---|
1 | Monitor |
2 | Mouse |
4 | Keyboard |
6 | Scanner |
. . . |
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!
