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( ).

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( );
  }
}

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.