Published: Saturday, June 10, 2000
Converting Hierarchical Recordsets Into XML, Part 3
By Richard Chisholm
Read Part 1
Read Part 2
Create_XMLNode() takes three parameters: a recordset, an XML Element, and an integer. Why the
integer? The integer corresponds to a place in an array that holds the names for the child elements. When
the function calls itself, 1 is added to the value to correspond with the movement into its child recordset.
Of course, be sure to check that you have enough spots in your array to match the depth of your recordset hierarchy,
or you will get an error.
'standard db connection and opening of recordset goes here....
Call Create_XMLNode(objRS, XMLRoot, 0)
|
Because this is the first call, the full recordset and the root XML element are passed. This is where things
get a little hairy. Because this is a recursive function we need to make sure that our objects get declared,
initialized, and destroyed properly.. For instance, when looking at the source code you will notice that in
the Convert() function, we open the recordset, but never close it. I found that when I included
the standard objRS.close, I would get a recordset error, and after further investigation I found
the recordset is closed in the Create_XMLNode function. Moving on, let's take a look at the
basic outline of the CreateXML_Node function, then go into the specifics.
Private Function Create_XMLNode(objRSet As ADODB.Recordset, _
XMLCurrentElement As IXMLDOMElement, _
ElementName As Integer)
'------------------------------------
'count the number of records
Do Until objRSet.EOF
For x = 0 To NumberOfRecords -1 'minus one because it starts at zero
IF this recordset field contains a child recordset THEN
'Get the child rs, create an element, and call Create_XMLNode
'to attach the child node(s) (this is discussed second)
ELSE
'Create and attach an element with no child nodes.
'(this is discussed first)
END IF
Next
Loop
|
Basically, this DO...LOOP runs through the recordset, and when it doesn't find a child recordset
goes ahead and creates a new XML tag. Looking at the passed values of this function, objRSet is
the portion of the recordset that is being used. This can be anything from the recordset containing multiple
child/grand child sets, or the last child recordset lying deep within. XMLCurrentElement (below)
represents the current node on the XML document, while ElementName is the position of the array.
The code for creating the new tag is:
Set XMLChild = XMLDoc.createElement(arrXMLElementName(ElementName))
If Not IsNull(objRSet(x).Value) Then XMLChild.Text = CStr(objRSet(x).Value)
XMLCurrentElement.appendChild XMLChild
XMLChild.setAttribute "Name", objRSet(x).Name
|
While this may look different from when we created the root element, it is only because we are dealing with
different data. The methods are the same. Using the createElement method we build the new tag,
and use the arrXMLElementName array to get the generic value of the tag, then we assign any text
with the .text property of new element. The CStr() around the value makes sure we
are returning a string. And since we need an identifying trait, a Name attribute is attached,
with the column name as its value. And as with the root tag, elements are attached to the document by using
the appendChild method on the current element. The above code will produce one tag that looks
like this (where CustomTag is a value retrieved from the array):
<CustomTag Name="RS Field Name">Text from recordset field</CustomTag>
|
When a child recordset is reached, the variable objChild is created, and its value is set to that
of the current column, which is the nested recordset. Then the parent tag is created, and the function is
called again.
If objRSet(x).Type = adChapter Then
Dim objChild As ADODB.Recordset
Set objChild = objRSet(x).Value 'Assign the child recordset
Set XMLChild = XMLDoc.createElement(arrXMLElementName(ElementName))
XMLCurrentElement.appendChild XMLChild
XMLChild.setAttribute "Name", objRSet(x).Name
Call Create_XMLNode(objChild, XMLChild, ElementName + 1)
Else
'Create tags (previous code mentioned above)
|
Most of the code here should be familiar, except for the line to call the function again. Notice we don't
assign any text to this node. Also, remember that the column name used here is declared in you SQL statement
using the AS alias command. When the function is called again, the child recordset is
passed not the whole recordset. Similarly, because the subsequent data to be attached to the XML document is
a child of the current element, XMLChild is passed to the function and becomes
XMLCurrentElement in the next sequence. Creating the tag and calling the function attach the
children looks like the following:
<CustomTag Name="RS Field Name">
<NewCustomTag Name="RS Field Name 1">Text 1</NewCustomTag>
<NewCustomTag Name="RS Field Name 2">Text 2</NewCustomTag>
<NewCustomTag Name="RS Field Name 2">Text 3</NewCustomTag>
<!-- etc... -->
</CustomTag>
|
Read Part 4 of "Converting Hierarchical Recordsets into XML"
