Bridging the Gap Between XML and Data Access

Retrieving XML Data from SQL Server 2000

More and more developers want to work with the results of their database queries as XML documents. To address this need, SQL Server 2000 added support for queries that return data in XML format. You could pull data into an ADO.NET DataSet and then use an XmlDataDocument object to access the data as an XML document, but this process involves more overhead than simply retrieving the data as XML.

Working with SELECT... FOR XML Queries

SQL Server 2000 added an optional FOR XML clause to queries, which lets you indicate that you want to retrieve the results of your query as XML. Let's look at a couple of examples that use this clause and discuss how you can retrieve the results of your query into an ADO.NET DataSet or an XmlDocument object.

Executing a SELECT...FOR XML Query in SQL Server Query Analyzer

The simplest way to execute such a query and examine the results is to execute the query in SQL Server Query Analyzer. Let's take a simple query that retrieves the values of the CustomerID and CompanyName columns for the first two rows in the Customers table

SELECT TOP 2 CustomerID, CompanyName FROM Customers

and append

" FOR XML AUTO, ELEMENTS"

to the query.

The FOR XML portion tells SQL Server to return the results of the query in XML format. AUTO tells SQL Server to name the element for each row in the result set after the table that is referenced in the query. ELEMENTS tells SQL Server to store the value of each column in an element. By default, SQL Server will return this information in attributes rather than elements.

Choose Results In Text from the Query menu, and then execute the query. The results will look like those shown in Figure 12-6, except that in the figure I've manually formatted the data to make it easier to read.

Figure 12-6

Executing a FOR XML query in SQL Server Query Analyzer

Of course, fetching XML data into SQL Server Query Analyzer isn't terribly helpful. Let's look at how to retrieve this data into more accessible objects using ADO.NET.

The OleDbCommand object is not designed to retrieve the results of FOR XML queries, but the SqlCommand object is. The SqlCommand object, which is part of the SQL Server Client .NET Data Provider, exposes an ExecuteXmlReader method that returns an XmlReader object, which you can use to access the results of the query.

Loading the Results of Your Query into a DataSet

The ADO.NET object model makes it simple to load the results of your query into a DataSet object. You can use the DataSet object's ReadXml method to read the data from the XmlReader object into your DataSet. The following code snippet demonstrates this functionality:

Visual Basic .NET

'Add the following lines of code at the beginning of the code module. Imports System.Data.SqlClient Imports System.Xml Dim strConn, strSQL As String strConn = "Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT TOP 2 CustomerID, CompanyName FROM Customers " & _          "FOR XML AUTO, ELEMENTS" Dim cn As New SqlConnection(strConn) cn.Open() Dim cmd As New SqlCommand(strSQL, cn) Dim rdr As XmlReader = cmd.ExecuteXmlReader Dim ds As New DataSet() ds.ReadXml(rdr, XmlReadMode.Fragment) rdr.Close() cn.Close() Dim strPathToXml As String = "C:\MyData.XML" ds.WriteXml(strPathToXml) ShowXmlInIE(strPathToXml)

Visual C# .NET

//Add the following lines of code at the beginning of the code module. using System.Data.SqlClient; using System.Xml; string strConn, strSQL; strConn = "Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT TOP 2 CustomerID, CompanyName FROM Customers " +          "FOR XML AUTO, ELEMENTS"; SqlConnection cn = new SqlConnection(strConn); cn.Open(); SqlCommand cmd = new SqlCommand(strSQL, cn); XmlReader rdr = cmd.ExecuteXmlReader(); DataSet ds = new DataSet(); ds.ReadXml(rdr, XmlReadMode.Fragment); rdr.Close(); cn.Close(); string strPathToXml = "C:\\MyData.XML"; ds.WriteXml(strPathToXml); ShowXmlInIE(strPathToXml);

You might have noticed that the call to the DataSet object's ReadXml method uses the constant Fragment in the second parameter. The results of the query are in XML format, but the results don't compose a well-formed XML document. Take another look at Figure 12-6, and you'll see that there is no single top-level node for the results. One requirement for an XML document to be well formed is that it have a single top-level node. The results of this query are considered an XML fragment. Thus, in the call to the ReadXml method, we specified that the information in the XmlReader object is a fragment.

Loading the Results of Your Query into an XmlDocument Object

If the results of the query were to represent a well-formed XML document, loading the data into an XmlDocument object would be simple. You'd only need to call the XmlDocument object's Load method and supply the XmlReader object.

Instead, we need to add a top-level node to the XmlDocument object and then append the results of the query to that top-level node, one node at a time. The following code snippet demonstrates this technique:

Visual Basic .NET

'Add the following lines of code at the beginning of the code module. Imports System.Data.SqlClient Imports System.Xml Dim xmlDoc As New XmlDocument() Dim nodRoot As XmlElement nodRoot = xmlDoc.AppendChild(xmlDoc.CreateElement("ROOT")) Dim strConn, strSQL As String strConn = "Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT TOP 2 CustomerID, CompanyName FROM Customers " & _          "FOR XML AUTO, ELEMENTS" Dim cn As New SqlConnection(strConn) cn.Open() Dim cmd As New SqlCommand(strSQL, cn) Dim rdr As XmlReader = cmd.ExecuteXmlReader Do Until rdr.EOF     nodRoot.AppendChild(xmlDoc.ReadNode(rdr)) Loop rdr.Close() cn.Close() Dim strPathToXml As String = "C:\MyData.XML" xmlDoc.Save(strPathToXml) ShowXmlInIE(strPathToXml)

Visual C# .NET

//Add the following lines of code at the beginning of the code module. using System.Data.SqlClient; using System.Xml; XmlDocument xmlDoc = new XmlDocument(); XmlElement nodRoot; nodRoot = xmlDoc.AppendChild(xmlDoc.CreateElement("ROOT")); string strConn, strSQL; strConn = "Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT TOP 2 CustomerID, CompanyName FROM Customers " +          "FOR XML AUTO, ELEMENTS"; SqlConnection cn = new SqlConnection(strConn); cn.Open(); SqlCommand cmd = new SqlCommand(strSQL, cn); XmlReader rdr = cmd.ExecuteXmlReader(); rdr.Close(); cn.Close(); string strPathToXml = "C:\\MyData.XML"; xmlDoc.Save(strPathToXml); ShowXmlInIE(strPathToXml);