When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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.

    - continued -

    '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)
          'Create and attach an element with no child nodes.
          '(this is discussed first)
        END IF

    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) 
      '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... -->

  • Read Part 4 of "Converting Hierarchical Recordsets into XML"

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article