11.3 Reading XML from a Database

In addition to writing the contents of a DataSet to an XML file, there are other ways to deal with a database's contents as XML. There are two general ways to do this: you can read the XML data directly, or you can read the data into a DOM tree.

11.3.1 Reading XML Data Directly

The SqlCommand class has another method that executes SQL queries, namely ExecuteXmlReader( ). ExecuteXmlReader( ) returns an instance of XmlReader that can be used to read the data.

ExecuteXmlReader( ) is a method of SqlCommand and not OleDbCommand (or the IDbCommand interface) because it uses the SQL Server for xml clause. The result of such a query is XML, which can then be read using the XmlReader instance. Example 11-10 shows a program to read data using ExecuteXmlReader( ).

The XmlReader returned from ExecuteXmlReader( ) is actually of the type XmlTextReader, because the data returned from the query is a text stream.

Example 11-10. Reading data using ExecuteXmlReader( )

using System;
using System.Data.SqlClient;
using System.Xml;

public class ReadDataAsXml {
  public static void Main(string [ ] args) {
    string command = "SELECT name, expiration_date, total_discount " +
      "FROM coupons, coupon_redemptions, customers " +
      "WHERE coupons.coupon_code = coupon_redemptions.coupon_code " + 
      "AND coupon_redemptions.customer_id = customers.customer_id " +
      "FOR XML AUTO";

    SqlCommand xmlCommand = new SqlCommand(command, connection);

    connection.Open( );
    XmlReader reader = xmlCommand.ExecuteXmlReader( );

    XmlDocument doc = new XmlDocument( );
    doc.Load(reader);
    doc.Save(Console.Out);

    connection.Close( );
  }
}

Like all XmlReader subclasses, the XmlReader returned from SqlCommand.ExecuteXmlReader( ) keeps the underlying data source open, which means that the SqlConnection remains open as long as the XmlReader is.

The resulting XML document output to the console is shown here:

<?xml version="1.0" encoding="IBM437"?>
<customers name="Mark's Roofing">
  <coupons expiration_date="2002-11-30T00:00:00">
    <coupon_redemptions total_discount="2.150000000000000e+001" />
    <coupon_redemptions total_discount="1.525000000000000e+001" />
  </coupons>
</customers>

It already looks different from the DiffGram. First, all the column values are represented as XML attributes rather than elements. Second, the numeric data values are shown with full precision.

SQL Server has a lot of built-in XML functionality, including the for xml clause, but it's outside of the scope of this book. For more information on selecting XML data directly from a SQL Server database, see chapter 8 of AppliedXML Programming for Microsoft .NET by Dino Esposito (Microsoft Press).

11.3.2 Reading Data Into a DOM Tree

There's another way to read XML directly from a database. The XmlDataDocument, which extends XmlDocument, presents the contents of a DataSet as an XML document. At that point, the data can be treated just like any other XmlDocument, including navigating to specific nodes with XPath, writing it to any sort of Stream, transforming it with XSLT, and in fact any of the other techniques I've shown you in this book. Example 11-11 shows a program that executes the same query from example 11-10 and writes the resulting XML to the console.

Example 11-11. Reading data as XML using XmlDataDocument

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

public class ReadDataAsXml {
  public static void Main(string [ ] args) {
    
    DataSet dataSet = new DataSet("AngusHardware");
    
    SqlConnection connection = new SqlConnection(
      "Initial Catalog=AngusHardware; User ID=sa");
    
    string command = "SELECT name, redemption_date, total_discount " +
      "FROM coupon_redemptions a, customers b " +
      "WHERE a.customer_id = b.customer_id";
    SqlDataAdapter adapter = new SqlDataAdapter(command, connection);
    
    adapter.Fill(dataSet, "CouponsRedeemed");

    XmlDataDocument doc = new XmlDataDocument(dataSet);   

    XmlTextWriter writer = new XmlTextWriter(Console.Out);
    writer.Formatting = Formatting.Indented;
    doc.WriteTo(writer);
  }
}

You've seen most of this before, but here's a quick look at some of the more important steps. First, you create the DataSet, the SqlConnection, a SQL select command, and the SqlDataAdapter:

DataSet dataSet = new DataSet("AngusHardware");
SqlConnection connection = new SqlConnection(
  "Initial Catalog=AngusHardware; User ID=sa");
string command = "SELECT name, redemption_date, total_discount " +
  "FROM coupon_redemptions a, customers b " +
  "WHERE a.customer_id = b.customer_id";
SqlDataAdapter adapter = new SqlDataAdapter(command, connection);

Next, you fill the adapter with data, naming the DataTable "CouponsRedeemed":

adapter.Fill(dataSet, "CouponsRedeemed");

Now, create an XmlDataDocument to wrap the DataSet. After this, doc is now ready to use, including all the base XmlDocument members:

XmlDataDocument doc = new XmlDataDocument(dataSet);

In this case, you can just use an XmlWriter to output the XmlDataDocument to the console:

XmlTextWriter writer = new XmlTextWriter(Console.Out);
writer.Formatting = Formatting.Indented;
doc.WriteTo(writer);

Other options include doing some sort of XPath query:

XmlNodeList nodes = doc.SelectNodes("//total_discount");
foreach (XmlElement element in nodes) {
  Console.WriteLine("Total discount is {0}", element.InnerText);
}

Unlike the XmlReader, the DataSet is a disconnected view of the data in the database. Once the DataSet is populated, you won't use up a database connection, so you can do as much with the DataSet as you want. The flip side of that is that all the data are stored locally, so large databases can use a lot of local storage.

Another downside of the XmlDataDocument is that XPath queries are less efficient than SQL Server's built-in search capabilities. Use it wisely.