Converting Shaped Recordsets into XML

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