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

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