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.

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.