Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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....."
			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
			
Total article views: 5736 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Supplement to 'Converting Shaped Recordsets into XML'

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

ARTICLE

Returning a Subset of a Recordset

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

FORUM

Filtering shaped recordsets

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

ARTICLE

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

FORUM

how to return a recordset execute by SP within the SP

how to return a recordset execute by SP within the SP

Tags
basics    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones