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

Converting Shaped Recordsets into XML

By Jon Winer,

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

			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

			set rs = nothing
			set conn = nothing

			Response.Write XMLObj.XML

			End Sub
Total article views: 5753 | Views in the last 30 days: 1
Related Articles

Supplement to 'Converting Shaped Recordsets into XML'

Jon Winer further discusses some comparisons using ADO versus the XML DOM to parse shaped recordsets...


Returning a Subset of a Recordset

Stateless programming can be tricky... In case you ever need to return a specified subset of a query...


More on Returning a Subset of a Recordset

Last week's tip created quite a discussion on different techniques for returning a subset of a recor...


Filtering shaped recordsets

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


how to return a recordset execute by SP within the SP

how to return a recordset execute by SP within the SP