10.6 Obtain an XML Document from a SQL Server Query

Problem

You need to execute a query against a SQL Server 2000 or MSDE database and retrieve the results as XML.

Solution

Specify the FOR XML clause on your SQL query to return the results as XML. Execute the command using the SqlCommand.ExecuteXmlReader method, which returns a System.Xml.XmlReader object through which you can access the returned XML data.

Discussion

SQL Server 2000 and MSDE provide direct support for XML. You simply need to add the clause FOR XML AUTO to the end of a SQL query to indicate that the results should be returned as XML. By default, the XML representation is not a full XML document. Instead, it simply returns the result of each record in a separate element, with all the fields as attributes. For example, the query

 SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO 

returns XML with the following structure:

 <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"/> <Customers CustomerID="ANTON" CompanyName="Antonio Moreno Taquera"/> <Customers CustomerID="GOURL" CompanyName="Gourmet Lanchonetes"/> 

Alternatively, you can add the ELEMENTS keyword to the end of a query to structure the results using nested elements rather than attributes. For example, the query

 SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO, ELEMENTS 

returns XML with the following structure:

 <Customers>   <CustomerID>ALFKI</CustomerID>   <CompanyName>Alfreds Futterkiste</CompanyName> </Customers> <Customers>   <CustomerID>ANTON</CustomerID>   <CompanyName>Antonio Moreno Taquera</CompanyName> </Customers> <Customers>   <CustomerID>GOURL</CustomerID>   <CompanyName>Gourmet Lanchonetes</CompanyName> </Customers> 

Note  

You can also fine-tune the format in more detail using the FOR XML EXPLICIT syntax. For example, this allows you to convert some fields to attributes and others to elements. Refer to the SQL Server Books Online for more information.

The following example demonstrates how to retrieve results as XML using the FOR XML clause and the ExecuteXmlReader method. Notice that the connection can't be used for any other commands while the XmlReader is open . You should process the results as quickly as possible and must always close the XmlReader . (Chapter 5 contains more detailed examples of how to use the XmlReader class.)

 using System; using System.Xml; using System.Data; using System.Data.SqlClient; public class XmlQueryExample {     public static void Main() {         // Create a new SqlConnection object.         using (SqlConnection con = new SqlConnection()) {             // Configure the SqlConnection object's connection string.             con.ConnectionString = "Data Source = localhost;" +                  "Database = Northwind; Integrated Security=SSPI";             // Create and configure a new command that includes the             // FOR XML AUTO clause.             SqlCommand com = con.CreateCommand();             com.CommandType = CommandType.Text;             com.CommandText = "SELECT CustomerID, CompanyName" +                  " FROM Customers FOR XML AUTO";             // Declare an XmlReader so that it can be referenced in the              // finally clause to ensure it is closed after use.             XmlReader reader = null;             try {                 // Open the database connection.                 con.Open();                 // Execute the command and retrieve an XmlReader to access                 // the results.                 reader = com.ExecuteXmlReader();                 while (reader.Read()) {                     Console.Write("Element: " + reader.Name);                     if (reader.HasAttributes) {                         for (int i = 0; i < reader.AttributeCount; i++) {                             reader.MoveToAttribute(i);                             Console.Write("  {0}: {1}",                                 reader.Name, reader.Value);                         }                         // Move the XmlReader back to the element node.                         reader.MoveToElement();                           Console.WriteLine();                     }                 }             } catch (Exception ex) {                 Console.WriteLine(ex.ToString());             } finally {                 // Ensure the reader is closed.                 if (reader != null) reader.Close();             }         }         // Wait to continue.         Console.ReadLine();     } } 

Some of the output from this test application is shown here:

 Element: Customers  CustomerID: ALFKI  CompanyName: Alfreds Futterkiste Element: Customers  CustomerID: ANTON  CompanyName: Antonio Moreno Taquera Element: Customers  CustomerID: GOURL  CompanyName: Gourmet Lanchonetes ... 

Instead of working with the XmlReader and accessing the data sequentially, you can read the XML data into a System.Xml.XmlDocument . This way, all the data is retrieved into memory, and the database connection can be closed. You can then continue to interact with the XML document. (Chapter 5 contains numerous examples of how to use the XmlDocument class.) Here's the code you would need.

 XmlDocument doc = new XmlDocument(); // Create a new SqlConnection object. using (SqlConnection con = new SqlConnection()) {     // Configure the SqlConnection object's connection string.     con.ConnectionString = "Data Source = localhost;" +          "Database = Northwind; Integrated Security=SSPI";     // Create and configure a new command that includes the     // FOR XML AUTO clause.     SqlCommand com = con.CreateCommand();     com.CommandType = CommandType.Text;     com.CommandText =          "SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO";     // Open the database connection.     con.Open();     // Load the XML data into the XmlDocument. Must first create a      // root element into which to place each result row element.     XmlReader reader = com.ExecuteXmlReader();     doc.LoadXml("<results></results>");     // Create an XmlNode from the next XML element read from the      // reader.     XmlNode newNode = doc.ReadNode(reader);     while (newNode != null) {         doc.DocumentElement.AppendChild(newNode);         newNode = doc.ReadNode(reader);     } } // Process the disconnected XmlDocument. Console.WriteLine(doc.OuterXml);