ADO.NET 3.5 Cookbook, 2nd Edition

9.9. Reading XML Data Directly from SQL Server

Problem

You need to read XML data directly from the SQL Server.

Solution

Use the FOR XML clause in the stored procedure or SQL statement.

The C# code in Program.cs in the project ReadXmlDirectlyFromSqlServer is shown in Example 9-15.

Example 9-15. File: Program.cs for ReadXmlDirectlyFromSqlServer solution

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

namespace ReadXmlDirectlyFromSqlServer
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT ContactID, FirstName, LastName " +
                "FROM Person.Contact WHERE ContactID BETWEEN 15 AND 20 " +
                "FOR XML AUTO, XMLDATA";

            DataSet ds = new DataSet(  );
            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                // Create the command.
                SqlCommand command = new SqlCommand(sqlSelect, connection);

                // Read the XML data into a XML reader.
                connection.Open(  );
                XmlReader xr = command.ExecuteXmlReader(  );

                // Read the data from the XML reader into the DataTable.
                ds.ReadXml(xr, XmlReadMode.Fragment);
                xr.Close(  );
            }

            Console.WriteLine("ContactID\tFirstName\tLastName");
            Console.WriteLine("---------\t---------\t--------");
            foreach (DataRow row in ds.Tables[0].Rows)
                Console.WriteLine("{0}\t\t{1}\t\t{2}",
                    row["ContactID"], row["FirstName"], row["LastName"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output ...