Supplement to 'Converting Shaped Recordsets into XML'

In early June, I wrote a brief article titled

Converting Shaped Recordsets into XML,

describing several applications

for converting a shaped recordset to XML and a quick 'How To'. In response

to several individuals' requests, I am writing this sequel to that article. This

article will elaborate on several of the methods discussed in my previous article

as well as incorporate some further examples.

To give a quick summary, the last article explained how using ADO, the

Shape Provider and the XML DOM, a recordset could be persisted into XML format.

This provided a way for users to maintain the hierarchy returned by the shaped recordset

and simultaneouly, have the flexiblility to display the generated XML in any desired

format by transforming it using an XSL stylesheet. I also briefly discussed, some comparisons

between using the XML DOM versus ADO to parse the hierarchical recordset/XML.

I'd first like to address using the XML DOM to navigate the XML document. Below is an expanded example

of the code from my previous article. (The examples will use the Northwind database).

Sub ProcessCustomerOrders()
Dim conn 
Dim rs 
Dim XMLObj 
Dim SqlString 
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
Set XMLObj = Server.CreateObject("MSXML2.DOMDocument")
conn.ConnectionString = "Provider=MSDataShape.1;....."
conn.Open
SqlString = "SHAPE {Select * from Customers} " & _
"APPEND ({Select * from Orders} as rsOrders " & _
"RELATE CustomerID to CustomerID)"
rs.Open SqlString, conn, adOpenDynamic, adLockOptimistic
'Save recordset out to the XML DOM
rs.Save XMLObj, adPersistXML
Response.Write XMLObj.xml
Call GetElements(XMLObj)
Set XMLObj = Nothing
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub

In this example, I am saving the shaped recordset out to the XML DOM, then

writing out the resultant XML to the browser window. The XML should look like this:

<xml>
<rs:data>
<z:row CustomerID="SIMOB" CompanyName="Simons bistro" .....>
<rsOrders OrderID="10341" CustomerID="SIMOB" ...../>
<rsOrders OrderID="10417" CustomerID="SIMOB" ...../>
<rsOrders OrderID="10556" CustomerID="SIMOB" ...../>
<rsOrders OrderID="10642" CustomerID="SIMOB" ...../>
<rsOrders OrderID="10669" CustomerID="SIMOB" ...../>
<rsOrders OrderID="10802" CustomerID="SIMOB" ...../>
<rsOrders OrderID="11074" CustomerID="SIMOB" ...../>
</z:row>
</rs:data>
<rs:data>
.
.
.
</rs:data>
.
.
.
</xml>

So now we have our XML and we can see that the shaped recordset's hierarchy

is maintained. Now, what if we wanted to return only the Order or Customer information?

We can use the XML DOM to navigate our XML to return the desired information.

In the example below, I will return the OrderID and CustomerID attribute values from the 'rsOrders' nodes using the same XML DOM object

used in the above example.

Sub GetElements(XMLObj)
Dim Order 
Dim Customer 
Dim J 
Dim iNodeListOrders 
 
Set iNodeListOrders = XMLObj.getElementsByTagName("rsOrders")
      
For J = 0 To iNodeListOrders.length - 1
      Order = iNodeListOrders.Item(J).Attributes.getNamedItem("OrderID").Text
      Customer = iNodeListOrders.Item(J).Attributes.getNamedItem("CustomerID").Text
      'Process order information
      Call SomeProcess(Order, Customer)
Next
End Sub

Now we've parsed our XML. The Order and Customer information we've just created

is nothing special to look at, but as shown

in the above example, once the information is obtained, it can be easily processed.

So far, we've seen how easily we can parse the XML and retrieve any

information we'd like using the XML DOM. One last item I'd like to address

before moving on to parsing the shaped recordset using ADO, is transforming

the XML by attaching a simple XSL stylesheet.

Dim NewXML
NewXML = XMLObj.transformNode(XSLStyleSheet)
Response.Write NewXML

By simply adding the lines of code above to the ProcessCustomerOrders sub, the XML

can be transformed to look like anything we want.

Here is an example of what the results might look like:

Customer SIMOB

     Order number: 10341

     Order number: 10417

     Order number: 10556

So up until now, we used soley the XML DOM to parse and display our desired XML

and output. Using our existing query and recordset, lets take a look at how to parse it using ADO.

For this example, I will return the CustomerID, CompanyName and OrderID.

While Not rs.EOF
    'Write the customer id and company name to the browser
    Response.Write rs.Fields("CustomerID") & ", " &  rs.Fields("CompanyName") & "<br>"
    'create a new recordset containing the child Order rows belonging to the current customer
    Set rsOrders = rs("rsOrders").Value
    'loop through the Orders
    While Not rsOrders.EOF
        'Write the Order number to the browser
        Response.Write rsOrders("OrderID") & "<br>"
        rsOrders.MoveNext
    Wend
    rsOrders.Close
    Set rsOrder = Nothing
    rs.MoveNext
Wend

Here are the results:

ALFKI, Alfreds Futterkiste
 10643 
 10692 
 10702 
 10835 
 10952 
 11011 
ANATR, Ana Trujillo Emparedados y helados
.
.
.

As you can see, we can return very similar results as we did using the XML DOM

and a stylesheet. In fact, we can achieve identical results. So which method

should you use? Just as in the garage workshop, you want to use the right tool for the

right job. In some cases, ADO will offer an advantage and other times the XML DOM will

provide the more elegant solution.

So what are some pros and cons of using the XML DOM versus ADO to parse a

shaped recordset?

While the above example looks rather simple, picture a shaped recordset with many different

hierarchies. The more complex the relationships between tables, the more nested the

above example would become. In addition, displaying a sophisticated interface to the data becomes

very complex and can often times become bound to the data. On the other hand, using the XML DOM allows us some

additional flexibility. With the XML DOM, multiple node lists can be created

to segregate information and easily retrieve information no matter the complexity

of the relationships. In addition, by using the XML DOM, we can transform the XML

for use by many other applications.

Despite XML's benefits, ADO is the better tool in certain situations. If I had a simple hierarchy, ADO provides

me a fast method for parsing and retrieving data. If there was no need to display the data,

but only process it, ADO most likely would be the better tool. Also, by employing recursive

programming techniques, you could use ADO to parse a fairly complex shaped recordset. And using the XML DOM does have its

drawbacks. There is a large amount of overhead incurred when using the XML DOM. Also, when it comes

to speed, ADO still supasses the XML DOM.

I hope this article was helpful and insightful. If you are interested in learning more about XML,

please see my homepage for links to my other articles.

In addition, Leon Platt has written a teriffic article title XML in 20 minutes.

Just to restate, the purpose of this article was not to suggest

one method over another, but to highlight the alternatives that the XML DOM offers to ADO.

As I mentioned in my related article, try and find a useful application for this

technology.