.NET & XML

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

Tip

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

Tip

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