Shaped recordsets in ADO provide a fantastic method for displaying relationships

between information contained in a relational database that cannot often times be

shown using T-SQL or Open XML.

In the example below, I submit a shaped recordset query via ADO to the server. This request returns

a shaped recordset. Using the 'Save' method of the recordset object, the recordset is saved to the XML DOM.

The 'adPersistXML' parameter saves the recordset as XML. Following this step, there are many alternatives

and possibilities on how to use the XML DOM to your advantage!

In my example, the xml is simply written out to the browser. But what are some other options. Well, one

would be to transform the xml using an xsl style sheet. This would provide immense flexibility in displaying

the output. Another benefit to loading the recordset into the XML DOM is the ease at which it can be parsed.

Ever try looping through a shaped recordset with the intention of only returning certain hierarchies of records?

It can be done, but think of how easy it would be using the XML DOM. Just specify a desired node list and

return the values.

I've only discussed two uses for loading a shaped recordset into the XML DOM, but there are many more

applications. See if you can come up with one.

Sub Get_CustomerOrders()
Dim conn,rs,SqlString
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
set XMLObj = Server.CreateObject("MSXML2.DOMDocument")
conn.ConnectionString = "Northwind....."
conn.Open
SQLString = "SHAPE {Select * from Customers} " & _
"APPEND ({Select * from Orders} as rsOrders " & _
"RELATE CustomerID to CustomerID)"
rs.Open SQLString,conn,1,1
rs.Save XMLObj,adPersistXML
rs.close
set rs = nothing
conn.close
set conn = nothing
Response.Write XMLObj.XML
End Sub