17.6 SQL Server 2000 XML

Finally, SQL Server also provides its own direct support for XML. By using the FOR XML clause in a SELECT query, you indicate that the results should be returned as XML. This technique is a bit of a compromise. Even though it provides XML-savvy development houses with an easy way to work natively with XML, it's also unavoidably specific to SQL Server, and therefore won't suit if you need the flexibility to migrate to (or incorporate data from) another platform such as Oracle or DB/2.

By default, the SQL Server XML representation isn't a full XML document. Instead, it simply returns the result of each record in a separate element, with all the fields as attributes (a marked different from ADO.NET's default, which includes all fields as elements).

For example, the query:

SELECT CategoryID, CategoryName, Description FROM Categories FOR XML AUTO

returns the following XML document:

<categories categoryID="1" categoryname="Beverages" description="Soft 
drinks, coffees, teas, beers, and ales"/>
<categories categoryID="2" categoryname="Condiments" description="Sweet and 
savory sauces, relishes, spreads, and seasonings"/>

<!-- Other categories omitted. -->

It's possible to reverse SQL Server's preference by adding the ELEMENTS keyword to the end of your query. For example, the query:

SELECT CategoryID, CategoryName, Description FROM Categories 
FOR XML AUTO ELEMENTS

returns the following document:

<Categories>
  <CategoryID>1</CategoryID>
  <CategoryName>Beverages</CategoryName>
  <Description>Soft drinks, coffees, teas, beers, and ales</Description>
</Categories>
<Categories>
  <CategoryID>2</CategoryID>
  <CategoryName>Condiments</CategoryName>
  <Description>Sweet and savory sauces, relishes, spreads, and 
   seasonings"/>
</Categories>

<!-- Other categories omitted. -->

Note that setting the format is an all-or-nothing decision. If you want to provide a more sophisticated XML document that follows a set format (i.e., some fields are represented as attributes, while others are columns) you must master the much more complex and much less compact FOR XML EXPLICIT syntax, which isn't described in this book. For more information, refer to SQL Server Books Online.

Finally, you can add the XMLDATA clause to return a pregenerated schema at the beginning of your document. However, this clause isn't of much use because the schema is based on Microsoft's XDR standard, which was proposed before the XSD standard was accepted. As a result, the schemas generated by SQL Server aren't recognized by most non-Microsoft XML parsers and will likely be replaced in future SQL Server versions.

If you want to retrieve a field with binary data, you can specify the BINARY BASE64 option at the end of your query. This returns the binary data as base-64 encoded text, rather than a reference. It also increases the size of the returned document.

In ADO.NET, you can retrieve this document using the SqlCommand.ExecuteXmlReader( ) method. This returns an XmlReader object that provides access to the returned XML.

Example 17-6 shows how to retrieve the query shown earlier and write it to a console window.

Example 17-6. Using SQL Server 2000 direct XML support

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

public class DirectXML
{
    private static string connectionString = "Data Source=localhost;" +
         "Initial Catalog=Northwind;Integrated Security=SSPI";

    public static void Main() 
    {
        string SQL = "SELECT CategoryID, CategoryName, Description " +
                     "FROM Categories FOR XML AUTO";

        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand(SQL, con);

        // Execute the command.
        try
        {
            con.Open();
            XmlReader reader = com.ExecuteXmlReader();
            while (reader.Read())
            {
 
                Console.WriteLine("Element: " + reader.Name);

                if (reader.HasAttributes)
                {
                    for (int i = 0; i < reader.AttributeCount; i++)
                    {
                        reader.MoveToAttribute(i);
                        Console.Write("\t");
                        Console.Write(reader.Name + ": ");
                        Console.WriteLine(reader.Value);
                    }

                    // Move back to the element node.
                    reader.MoveToElement();

                    Console.WriteLine();
                }
            }
            reader.Close();
        }
        catch (Exception err)
        {
            Console.WriteLine(err.ToString());
        }
        finally
        {
            con.Close();
        }
    }
}

The results for the first two rows are shown here:

Element: Categories
        CategoryID: 1
        CategoryName: Beverages
        Description: Soft drinks, coffees, teas, beers, and ales

Element: Categories
        CategoryID: 2
        CategoryName: Condiments
        Description: Sweet and savory sauces, relishes, spreads

One other interesting ability of the FOR XML AUTO command is that it automatically infers relations with JOIN queries and creates XML documents with a nested structure. For example, the query:

SELECT CategoryName, ProductName Description FROM Categories INNER JOIN 
Products ON Products.CategoryID = Categories.CategoryID FOR XML AUTO

creates the following XML document:

<Categories CategoryName="Beverages">
  <Products Description="Chai"/>
  <Products Description="Chang"/>
</Categories>
<Categories CategoryName="Condiments">
  <Products Description="Aniseed Syrup"/>

<!-- Other categories and products omitted. -->

To disable this behavior, use the FOR XML RAW syntax instead, which always returns a rigid single-grid XML result. The XML RAW option also gives every row element the name row instead of the name of the table (for example, Categories).

You can also use variations of the FOR XML EXPLICIT syntax to specify nearly every aspect of how the returned XML document should look, and the OPENXML statement to retrieve an XML document from a file and process it in a stored procedure. For more information about the direct support for XML in SQL Server, consult the SQL Server 2000 Books Online.

17.6.1 The SQLXML Provider

Microsoft also provides a special ADO.NET provider designed exclusively with SQL Server and its XML support in mind. This provider isn't included with .NET, although you can download it online from MSDN at http://msdn.microsoft.com/downloads/sample.asp?url=/msdn-files/027/001/824/msdncompositedoc.xml.

The SQLXML provider isn't in all respects a true ADO.NET provider. For example, it provides only three managed objects: SqlXmlCommand, SqlXmlParameter, and SqlXmlAdapter. These objects don't implement the standard interfaces, and there is no collection class (it is encapsulated by SqlXmlCommand).

The SqlXmlCommand class is the heart of the SQLXML provider. You choose the format of command by setting the SqlXmlCommand.CommandType property. Table 17-6 lists valid CommandType values.

Table 17-6. SqlXmlCommandType values

Property	Description
Sql	Uses SQL text (similar to the standard SQL Server provider), for example: SELECT * FROM Employees FOR XML AUTO
XPath	Uses an XPath query; for example, Employees[@EmployeeID=1]
Template	Executes a SQL command defined in an XML template document
TemplateFile	Executes a SQL command defined in an XML template document that's stored in a file
UpdateGram	Directly executes an UpdateGram, which is a predecessor to the DiffGram
Diffgram	Directly executes an ADO.NET DiffGram, which defines DataSet changes

When you use SQLXML with SQL Server and XML, you have several options. You can:

• Transform rowsets to XML on the client side, not the server side. This can lessen the work the server needs to perform.

• Submit XPath queries directly (rather than first retrieving the XML document and than searching it).

• Submit batch updates as a DiffGram.

We'll concentrate on these three features in the remainder of this chapter. In addition, the SQLXML provider duplicates some features provided by the standard SQL Server provider, such as the ability to execute a FOR XML query and capture the results with an XmlReader.

17.6.2 Converting to XML on the Client-Side

With FOR XML queries, SQL Server performs a query, converts it to XML, and returns the XML stream to the client. This has the potential for a minor performance penalty, and the network bandwidth required to send an XML document is always greater than that required for SQL Server's optimized TDS interface, which sends a stream of proprietary binary data. To reduce this effect in performance-sensitive applications, you can use the SQLXML provider's ability to convert a result set to XML on the client side. The resulting document takes the exact same form as if the server had performed the work.

The following code snippet shows this technique:

string connectionString = "Data Source=localhost;" +
  "Initial Catalog=Northwind;Integrated Security=SSPI";

// Create the command (which encapsulates a connection).
SqlXmlCommand cmd = new SqlXmlCommand(connectionString);

// Create the XML on the client.
cmd.ClientSideXml = true;

// Define the command.
cmd.CommandText = "SELECT * FROM Customers FOR XML AUTO";

// Get the XML document.
XmlReader r = cmd.ExecuteReader();

One reason to use the ClientSideXml property is to wrap a stored procedure that doesn't return an XML document; the data will convert to XML seamlessly. For example, consider the following stored procedure that retrieves a list of customers and the products they have ordered:

CREATE PROCEDURE CustOrderHist (@CustomerID nchar(5))
AS

SELECT ProductName, Total=SUM(Quantity)
 FROM Products P, [Order Details] OD, Orders O, Customers C
 WHERE C.CustomerID = @CustomerID
 AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID 
 AND OD.ProductID = P.ProductID
 GROUP BY ProductName

GO

You can execute this stored procedure and convert the result to an XML document on the client side with the following code:

string connectionString = "Data Source=localhost;" +
  "Initial Catalog=Northwind;Integrated Security=SSPI";

SqlXmlCommand cmd = new SqlXmlCommand(connectionString);

SqlXmlParameter p = cmd.CreateParameter();
p.Value = "ALFKI";

// Define the command.
cmd.CommandText = "exec CustOrderHist ? FOR XML AUTO";
cmd.ClientSideXml = true;

// Get the XML document.
XmlReader r = cmd.ExecuteReader();

This example also illustrates the slightly different code used to call stored procedures with the SQLXML provider. Unlike other ADO.NET providers, you don't need to define the data type of the parameters used.

17.6.3 Submitting Direct XPath Queries

With the ordinary SQL Server provider, you must retrieve data using a SQL query before you can search it with XPath. The SQLXML provider removes this restriction. Performing an XPath query is as easy as setting the SqlXmlCommand.XPath property.

For example, consider the following XML document that defines an XPath query to select the FirstName and LastName fields from the Customers table:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Emp" sql:relation="Employees" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="FName"  
                     sql:field="FirstName" 
                     type="xsd:string" /> 
        <xsd:element name="LName"  
                     sql:field="LastName"  
                     type="xsd:string" />
     </xsd:sequence>
     <xsd:attribute name="EmployeeID" type="xsd:integer" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

You can use the defined Emp XPath query in a SqlXmlCommand as follows:

string connectionString = "Data Source=localhost;" +
  "Initial Catalog=Northwind;Integrated Security=SSPI";

SqlXmlCommand cmd = new SqlXmlCommand(connectionString);

SqlXmlParameter p = cmd.CreateParameter();
p.Value = "ALFKI";

// Define the command.
cmd.CommandText = "Emp";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.RootTag = "ROOT";
cmd.SchemaPath = "XPathDoc.xml";

// Get the XML document.
XmlReader r = cmd.ExecuteReader();

Note that the XPath document is loaded from the file identified by SqlXmlCommand.SchemaPath.

17.6.4 Batch Updates with the DiffGram

The SQLXML provider can also submit changes in a single batch operation using a DiffGram. In fact, if you use the SqlXmlAdatper to update a data source from a DataSet, this behavior takes place automatically, although you may not realize it.

For example, consider the following snippet of code that fills a DataSet and then applies changes to the data source:

SqlXmlCommand cmd = new SqlXmlCommand(connectionString);
cmd.CommandText = "SELECT * FROM Customers FOR XML AUTO";

SqlXmlAdapter adapter = new SqlXmlAdapter(cmd);

DataSet ds = new DataSet();

// Fill the DataSet.
adapter.Fill(ds);

// (Modify the DataSet here.)

// Apply changes using the DiffGram.
ad.Update(ds);

When the SqlXmlAdapter.Update( ) method is invoked, the SqlXmlAdapter doesn't step through the rows one by one looking for changes. Instead, it receives the DiffGram directly, and submits that document. The process is transparent to the .NET programmer.