Published: Wednesday, June 06, 2001
Creating a Function to Stream a Recordset to XML
By Scott Mitchell
Read Part 1
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!
By Scott Mitchell
Related Documents:
View the live demo!
Visit the XML Article Index
Visit XML101.com
