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.
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, spreadsOne 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.
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.
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.
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.
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.
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.