Using the SQLXML Managed Classes, Page 2
Retrieving Valid XML Data from SQL Server
My second example shows how to fix a problem with SQL Server's native FOR XML
queries. FOR XML doesn't actually return an XML document; instead, it returns an
XML fragmant, with no XML declaration or root element. This is a problem if you
want to feed this XML to classes that expect actual XML. Fortunately, you can
SqlXmlCommand object to execute a FOR XML query and wrap up
the results properly on their way in to your code.
For this example, I've constructed a FOR XML query that returns some customer
and order information. Using the SQLXML Managed Classes, I can retrieve the
results of this query straight to an
XmlReader and display them on
the test form's user interface. Here's the code:
Private Sub btnLoadXML_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLoadXML.Click ' Load proper XML from SQL Server Dim strConn As String = "Provider=SQLOLEDB;" & _ "Server=(local);database=Northwind;" & _ "Integrated Security=SSPI" ' Define the XML command Dim cmd As SqlXmlCommand = New SqlXmlCommand(strConn) ' Set up a SqlXmlCommand with a FOR XML query cmd.CommandType = SqlXmlCommandType.Sql cmd.CommandText = _ "SELECT Customers.CompanyName, " & _ "Customers.ContactName, " & _ "Orders.OrderID, Orders.OrderDate " & _ "FROM Customers INNER JOIN Orders " & _ "ON Customers.CustomerID = " & _ "Orders.CustomerID " & _ "FOR XML AUTO, ELEMENTS" ' Specify a root element for the result cmd.RootTag = "dataroot" ' Read the XML into an XmlReader Dim xr As XmlReader = _ cmd.ExecuteXmlReader() ' Transfer it to an XmlDOcument Dim xd As XmlDocument = New XmlDocument xd.Load(xr) xr.Close() ' Get a Transform ready Dim xslt As XslTransform = New XslTransform xslt.Load("identity.xslt") ' Use the transform to pretty print it Dim ms As MemoryStream = New MemoryStream xslt.Transform(xd, Nothing, ms) ' And drop the results to a TextBox control ms.Position = 0 Dim sr As StreamReader = New StreamReader(ms) txtXML.Text = sr.ReadToEnd() End Sub
Most of this is just standard .NET XML code. What SQLXML brings to the table
is the ability to get a true XML stream from a SQL Server query. That's what the
ExecuteXmlReader method accomplishes.
identity.xslt file, by the way, is a simple XSL stylesheet
that adds indentation to the input file without making any other changes. It's
useful whenever you want to display XML in a prettier fashion:
<?xml version = "1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output method="xml" indent="yes"/> <xsl:template match="node()|@*"> <xsl:copy> <xsl:apply-templates select="node()|@*"/> </xsl:copy> </xsl:template> </xsl:stylesheet>
A Good Interim Solution
Some time next year, SQL Server 2005 is going to deliver tightly-integrated XML functionality, as well as much closer .NET integration. But that's next year, and you probably have applications to write now. Until the new stuff is available, the SQLXML library provides a well-tested set of ways to work with SQL Server 2000 and XML that you can use today.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his MCAD 70-305, MCAD 70-306, and MCAD 70-310 Training Guides from Que Publishing. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.