SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Supplement to 'Converting Shaped Recordsets into XML'

By Jon Winer,

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;....."

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
Set rs = Nothing
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:

		<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" ...../>


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)

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>"
    Set rsOrder = Nothing

Here are the results:

ALFKI, Alfreds Futterkiste
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.

Total article views: 3843 | Views in the last 30 days: 1
Related Articles

Converting Shaped Recordsets into XML

This article shows you how to take a shaped recordset and convert the results into XML all while pre...


Filtering shaped recordsets

Hi, Can anyone help me with a SQL data shaping problem thats taken away most of my day! I want to ...


Introduction to ADO Part 2 - Recordsets

Part two in the Introduction to ADO series, this beginner level article shows how to open a recordse...


XML Workshop XVI - Shaping the XML results

Continuing on with his series, Jacob Sebastian shows how you can shape the query results to a certai...


Triggers Examples

Triggers Examples