Creating a Function to Stream a Recordset to XML

In Part 1 we examined the impetus for this article and looked at how to call our custom function, ConvertRStoXML. We also examined what to expect back from the function (a string containing an XML representation of the Recordset object passed in). All that remains, now, is to write the code for this custom function, which we'll tackle in this part.

Writing the Code for ConvertRStoXML
Since the ConvertRStoXML function needs to build up an XML document from the structure of a Recordset, we need to use the XMLDOM component from Microsoft. This should already be installed on your Web server - it comes preinstalled with Internet Explorer and a bevy of other service packs and common Windows programs. If you are not familiar with XML, I suggest you check out some of the articles on the XML Article Index or, better yet, mosey on over to XML101.com and read some of the beginner-level tutorials. This article will explain using the XMLDOM object some, but not delve into significant detail. You may also wish to read Saving HTML Form Data to XML, which presents a function ConvertFormtoXML; the function in this article is very similar in both syntax and semantics.

To start our function off, we'll begin by Dimming our variables and creating an instance of the XMLDOM object:

Function ConvertRStoXML(objRS, strTopLevelNodeName, strRowNodeName) 'Declare local variables. Dim objDom Dim objRoot Dim objField Dim objFieldValue Dim objcolName Dim objattTabOrder Dim objPI Dim x Dim objRSField Dim objRow 'Instantiate the Microsoft XMLDOM. Set objDom = server.CreateObject("Microsoft.XMLDOM") objDom.preserveWhiteSpace = True

Simple enough. Next, we need to create the root element of the XML document. The name for this root node is supplied by the strTopLevelNodeName parameter:

'Create your root element and append it to the XML document. Set objRoot = objDom.createElement(strTopLevelNodeName) objDom.appendChild objRoot

Now for the fun part! We need to iterate through each row of our Recordset - we'll accomplish this using a standard Do While Not objRS.EOF ... Loop. At each iteration through this Do ... Loop we'll need to create a row-level node (with the name specified by the strRowNodeName parameter), which will need to be appended to the root node.

Also, as we iterate through each row of the Recordset, we'll need to iterate through each field of the particular row. This is accomplished via a For Each ... Next loop. In each iteration of this For Each ... Next loop, a field node is created. This node is given a name attribute that corresponds to the name of the field. Yet another node is created in each iteration, a value node, which stores the value of the field. This value node is appended to the field node, which is then appended to the row-level node. Phew! That's a lot of appending. Hopefully the code sample below helps remove any lingering confusion:

'Iterate through each row in the Recordset Do While Not objRS.EOF 'Create a row-level node with the name specified by strRowNodeName Set objRow = objDom.CreateElement(strRowNodeName) 'Iterate through each field in the Recordset row For Each objRSField in objRS.Fields '*** Create an element, "field". *** Set objField = objDom.createElement("field") '*** Append the name attribute to the field node *** Set objcolName = objDom.createAttribute("name") objcolName.Text = objRSField.Name objField.SetAttributeNode(objColName) '*************************************************** '*** Create a new node, "value". *** Set objFieldValue = objDom.createElement("value") 'Set the value of the value node equal to the value of the 'current field object objFieldValue.Text = objRSField.Value '************************************ '*** Append the value node as a child of the field node. *** objField.appendChild objFieldValue '*********************************************************** '*** Append the field node as a child of the row-level node. *** objRow.appendChild objField '*************************************************************** Next '*** Append the row-level node to the root node *** objRoot.appendChild objRow '************************************************** objRS.MoveNext 'Move to the next row in the Recordset Loop

At this point, all that's left to do is add the <?xml version="1.0" ?> tag to the top of the XML document and return the XML contents as a string. This is accomplished with the following code:

'*** Add the <?xml version="1.0" ?> tag *** Set objPI = objDom.createProcessingInstruction("xml", "version='1.0'") 'Append the processing instruction to the XML document. objDom.insertBefore objPI, objDom.childNodes(0) '************************************************ 'Return the XML contents as a string ConvertRStoXML = objDom.xml 'Clean up... Set objDom = Nothing Set objRoot = Nothing Set objField = Nothing Set objFieldValue = Nothing Set objcolName = Nothing Set objattTabOrder = Nothing Set objPI = Nothing End Function

And there you have it! Be sure to check out the live demo. Also, when you do, compare the clean XML produced by ConvertRStoXML as compared to the very unreadable XML produced by the Recordset's built-in Save method.

Happy Programming!