Section 6.2. SQLXML 4.0

6.2. SQLXML 4.0

SQL Server 2005 introduces SQLXML 4.0, which provides functionality found in previous releases and adds support for new SQL Server 2005 XML and web service functionality. SQLXML 4.0 is installed automatically with SQL Server 2005. You can also install SQLXML on clients' computers from the installation program sqlxml4.msiyou will also need to install MSXML 6.0 from the installation program msxml6.msi. These installation programs are on the SQL Server 2005 installation disk in the Tools\Setup subdirectory.

SQLXML 4.0 provides client-side XML functionality for writing applications that access XML data from SQL Server, process it, and send updated data back to the server. SQLXML bridges relational data to XML data in SQL Server. SQLXML is used to query relational data using T-SQL and return XML results, query relational data with XPath, and update relational data using XML. In addition to facilitating working with relational data as XML, SQLXML lets you execute XML template queriesqueries embedded in a dynamic XML documentand server-side XPath queries in SQL Server 2005. The SQL Server .NET data provider, without SQLXML, does not provide this capability.

SQLXML 4.0 supports both the SQL Native Client and SQLOLEDB providers. The SQL Native Client provider is recommended, because it supports new SQL Server 2005 features such as the xml data type.

6.2.1. SQLXML Managed Classes

SQLXML managed classes expose SQLXML 4.0 functionality within .NET applications. The SQLXML managed classes provide methods to execute commands (SqlXmlCommand), create parameters for commands (SqlXmlParameter), and interact with the DataSet class (SqlXmlAdapter). These classes are described in the following subsections.

6.2.1.1. SqlXmlCommand

The SqlXmlCommand class executes a T-SQL command, stored procedure, XPath command, XML template file, UpdateGram, or DiffGram against a database. (UpdateGrams and DiffGrams are discussed later in this chapter.)

The constructor for the SqlXmlCommand object is:

     SqlXmlCommand(string connectionString) 

where connectionString is an OLE DB connection string identifying the provider, server, database, and login information. For example:

     Provider=SQLNCLI;Server=(local);database=AdventureWorks;Integrated Security=SSPI 

You should normally set the Provider property of the connection to SQLNCLI, because the SQL Native Client data provider supports new SQL Server 2005 features such as the xml data type.

The SqlXmlCommand class has the public methods described in Table 6-3.

Table 6-3. SqlXmlCommand class public methods

Method	Description
ClearParameters( )	Removes SqlXmlParameter objects created for the command object
CreateParameter( )	Creates and returns a SqlXmlParameter object for the command object
ExecuteNonQuery( )	Executes the command and returns nothing
ExecuteStream( )	Returns a new Stream object containing the results of a query
ExecuteToStream(Stream)	Returns results to an existing Stream object
ExecuteXmlReader( )	Returns an XmlReader object containing the results of a query

The SqlXmlCommand class has the public properties described in Table 6-4.

Table 6-4. SqlXmlCommand class public properties

Property	Description
BasePath	A directory path used to resolve a relative mapping schema path (specified by the SchemaPath property), a relative XSL file path (specified by the XslPath property), or an external schema reference in an XML template (specified using the mapping-schema attribute).
ClientSideXml	Specifies whether the result set is converted to XML on the client side (middle tier).
CommandStream	A file containing the text of the command to execute. If a CommandStream file is used, only DiffGram, Template, and UpdateGram update CommandType values are allowed.
CommandText	The command text to execute. You can execute stored procedures with the SqlXmlCommand object by specifying the command text as follows: EXEC storedProcedureName [ ? [ , ... n] ] where the question mark (?) represents one or more parameters: instances of the SqlXmlParameter class, discussed in the next section.
CommandType	The type of command to execute. One of the following values from the SqlXmlCommandType enumeration: Sql A T-SQL command (text or stored procedure) XPath An XPath command Template An XML template TemplateFile An XML template at the specified path UpdateGram An UpdateGram Diffgram A DiffGram
Namespaces	The namespaces for an XPath query.
OutputEncoding	The encoding for the returned stream. UTF-8 is the default.
RootTag	The root-level tag for returned XML results. If the command results in an XML fragment, this property can be used to make it a valid XML document.
SchemaPath	The name of the mapping schema for an XPath query, including the directory path. The path can be absolute or relative. If it is relative, the BasePath property is used to resolve the relative path.
XslPath	The name of the XSL transformation file, including the directory path to apply to the XML result set. The path can be absolute or relative. If it is relative, the BasePath property is used to resolve the relative path.

6.2.1.2. SqlXmlParameter

SqlXmlCommand objects support parameterized command text and stored procedure queries. Call the CreateParameter( ) method of the SqlXmlCommand class to create a parameter. The SqlXmlParameter class has the public properties described in Table 6-5.

Table 6-5. SqlXmlParameter class public properties

Property	Description
Name	Gets or sets the name of the parameter
Value	Gets or sets the value of the parameter

6.2.1.3. SqlXmlAdapter

The SqlXmlAdapter class is similar to the DataAdapter class. It provides a mechanism to load XML data into a DataSet and subsequently update the database with changes made offline to the data in the DataSet.

The SqlXmlAdapter has the following public constructors:

     SqlXmlAdapter(SqlXmlCommand command)     SqlXmlAdapter(string commandText, SqlXmlCommandType commandType,       string connectionString)     SqlXmlAdapter(Stream commandStream, SqlXmlCommandType commandType,       string connectionString) 

The SqlXmlAdapter has the public methods described in Table 6-6.

Table 6-6. SqlXmlAdapter public properties

Method	Description
Fill( )	Executes a SqlXmlCommand object and loads the result set returned by that command into a DataSet object.
Update( )	Propagates disconnected changes made to the DataSet object back to the database server

6.2.2. Using SQLXML Managed Classes

This section contains examples showing how to use the SQLXML managed classes to retrieve, process, output, and update data.

Your Visual Studio 2005 projects require a reference to the Microsoft.Data.SqlXml namespace to use SQLXML managed classes.

6.2.2.1. Executing a query

The following example reads the top two employees from the HumanResources.Employee table in AdventureWorks into a Stream object:

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandText = "SELECT TOP 2 * FROM HumanResources.Employee " +                 "FOR XML AUTO";             Stream stream = cmd.ExecuteStream(  );             StreamReader sr = new StreamReader(stream);             string s = sr.ReadToEnd(  );             Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

The example uses the StreamReader class to read the Stream into a string that is then output to the console. Results follow:

     <HumanResources.Employee Employee NationalIDNumber="14417807"       Contact Login Manager       Title="Production Technician- WC60" BirthDate="1972-05-15T00:00:00"       MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00"       SalariedFlag="0" VacationHours="21" SickLeaveHours="30"       CurrentFlag="1" rowgu       ModifiedDate="2004-07-31T00:00:00"/>     <HumanResources.Employee Employee NationalIDNumber="253022876"       Contact Login Manager       Title="Marketing Assistant" BirthDate="1977-06-03T00:00:00"       MaritalStatus="S" Gender="M" HireDate="1997-02-26T00:00:00"       SalariedFlag="0" VacationHours="42" SickLeaveHours="41"       CurrentFlag="1" rowgu       ModifiedDate="2004-07-31T00:00:00"/> 

6.2.2.2. Using parameters in a query

The following example uses a parameterized query to read the data for an employee:

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandText = "SELECT * FROM HumanResources.Employee " +                 "WHERE EmployeeID=? FOR XML AUTO";             // create the parameter to read data for EmployeeID = 5             SqlXmlParameter parm = cmd.CreateParameter(  );             parm.Value = "5";             Stream stream = cmd.ExecuteStream(  );             StreamReader sr = new StreamReader(stream);             string s = sr.ReadToEnd(  );             Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

OLE DB queries use a question mark (?) placeholder to identify parameters in the query. The parameters in the query string are replaced in the same order in which they are created from the SqlXmlCommand object using the CreateParameter( ) method.

Formatting with the FOR XML Clause

SQL Server 2005 lets you format the results of a T-SQL query into an XML document on either the server side or client side. You do this by using the SELECT statement's FOR XML clause. You can specify one of three FOR XML modes for server-side XML formatting:

RAW

A single <row> element is returned in the XML document for each row in the result set.

AUTO

An XML document is returned with a hierarchy automatically created based on the way the SELECT statement is specified. Nested FOR XML queries can overcome the limitations for the hierarchy that is automatically generated.

EXPLICIT

An XML document is returned based on shape information in the query. Shape information lets you manipulate result set rows into hierarchies. EXPLICIT mode lets you create complex, custom XML structures for the result set.

Client-side XML formatting formats a SQL query's result set into XML at the client rather than at the SQL Server. There are three client-side FOR XML modes:

RAW

Identical to server-side RAW mode.

NESTED

Similar to server-side AUTO mode with the following exceptions:

A query against a view returns the base table name as the element name when using client-side formatting. When using server-side formatting, the view name is returned as the element name.

A query against an aliased table returns the base table name as the element name when using client-side formatting. When using server-side formatting, the table alias is returned as the element name.

EXPLICIT

Similar to server-side EXPLICIT mode.

You cannot use client-side formatting with queries that return multiple result sets. However, in an XML template, you can specify more than one <sql:query> block, each containing a query that returns a single result set.

If you specify FOR XML AUTO mode in a query, XML formatting occurs on the server even if client-side formatting is otherwise specified.

The results follow:

     <HumanResources.Employee Employee NationalIDNumber="480168528"       Contact Login Manager       Title="Tool Designer" BirthDate="1949-08-29T00:00:00" MaritalStatus="M"       Gender="M" HireDate="1998-01-11T00:00:00" SalariedFlag="0"       VacationHours="9" SickLeaveHours="24" CurrentFlag="1"       rowgu       ModifiedDate="2004-07-31T00:00:00"/> 

6.2.2.3. Handling an exception

The following example shows how to handle a SqlXmlException exception. This example modifies the example in the preceding sectionthe parameter expected in the query string is no longer created and, as a result, the code raises an exception when executed.

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandText = "SELECT * FROM HumanResources.Employee " +                 "WHERE EmployeeID=? FOR XML AUTO";             try             {                 stream = cmd.ExecuteStream(  );                 StreamReader sr = new StreamReader(stream);                 string s = sr.ReadToEnd(  );                 Console.WriteLine(s);             }             catch (SqlXmlException ex)             {                 Console.WriteLine(ex.Message);             }             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 6-1.

Figure 6-1. Results for exception handling example

6.2.2.4. Retrieving an XmlReader object

The following example reads the data for the first two employees from the HumanResources.Employee table in AdventureWorks into an XmlTextReader object:

     using System;     using Microsoft.Data.SqlXml;     using System.Xml;     class Program     {         static void Main(string[] args)         {             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandText = "SELECT TOP 2 * FROM HumanResources.Employee " +                 "FOR XML   AUTO";             XmlReader xr = cmd.ExecuteXmlReader(  );             using (XmlTextWriter xtw = new XmlTextWriter(Console.Out))             {                 xtw.WriteNode(xr, false);                 xtw.Flush(  );             }             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

The example uses an XmlTextWriter object to output the results in the XmlReader object to the console. Results follow:

     <HumanResources.Employee Employee NationalIDNumber="14417807"       Contact Login Manager       Title="Production Technician- WC60" BirthDate="1972-05-15T00:00:00"       MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00" SalariedFlag="0"       VacationHours="21" SickLeaveHours="30" CurrentFlag="1"       rowgu       ModifiedDate="2004-07-31T00:00:00" />     <HumanResources.Employee Employee NationalIDNumber="253022876"       Contact Login Manager       Title="Marketing Assistant" BirthDate="1977-06-03T00:00:00"       MaritalStatus="S" Gender="M" HireDate="1997-02-26T00:00:00" SalariedFlag="0"       VacationHours="42" SickLeaveHours="41" CurrentFlag="1"       rowgu       ModifiedDate="2004-07-31T00:00:00" /> 

6.2.2.5. Processing an XML result set on the client

The following example returns a result set and uses the ClientSideXml property of the SqlXmlCommand object to process the results at the client side. It produces an XML document formatted using the FOR XML NESTED mode.

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             Stream stream;             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandText = "SELECT TOP 2 * FROM HumanResources.Employee " +                 "FOR XML NESTED";             cmd.ClientSideXml = true;             stream = cmd.ExecuteStream(  );             StreamReader sr = new StreamReader(stream);             string s = sr.ReadToEnd(  );             Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

You can also specify a stored procedure that returns a regular non-XML result set. Results follow:

     <Employee Employee NationalIDNumber="14417807" Contact       Login Manager       Title="Production Technician - WC60" BirthDate="1972-05-15T00:00:00"       MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00"       SalariedFlag="0" VacationHours="21" SickLeaveHours="30" CurrentFlag="1"       rowgu       ModifiedDate="2004-07-31T00:00:00"/>     <Employee Employee NationalIDNumber="253022876" Contact       Login Manager       Title="Marketing Assistant" BirthDate="1977-06-03T00:00:00"       MaritalStatus="S" Gender="M" HireDate="1997-02-26T00:00:00"       SalariedFlag="0" VacationHours="42" SickLeaveHours="41" CurrentFlag="1"       rowgu       ModifiedDate="2004-07-31T00:00:00"/> 

6.2.2.6. Filling a DataSet

The following example uses an XmlDataAdapter object to fill a DataSet object:

     using System;     using Microsoft.Data.SqlXml;     using System.Data;     class Program     {         static void Main(string[] args)         {             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandText = "SELECT TOP 2 * FROM HumanResources.Employee " +                 "FOR XML AUTO";             DataSet ds = new DataSet(  );             SqlXmlAdapter da = new SqlXmlAdapter(cmd);             da.Fill(ds);             foreach (DataRow row in ds.Tables[0].Rows)                 Console.WriteLine("{0} {1}", row["EmployeeID"], row["Title"]);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 6-2.

Figure 6-2. Results for filling a DataSet example

6.2.2.7. Applying an XSLT transformation to the XML result set

The following example applies an XSLT transformation to the XML result set containing the top 10 employees from the HumanResources.Employee table in AdventureWorks. The XSL file named C:\PSS2005\Employee.xsl follows:

     <?xml version='1.0' encoding='UTF-8'?>      <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">         <xsl:output method="html"/>         <xsl:template match='*'>             <xsl:apply-templates />         </xsl:template>         <xsl:template match='HumanResources.Employee'>            <TR>              <TD><xsl:value-of select='@EmployeeID'/></TD>              <TD><xsl:value-of select='@Title'/></TD>            </TR>         </xsl:template>         <xsl:template match='/'>           <HTML>             <HEAD/>             <BODY>              <TABLE>                <TR>                  <TH>Employee ID</TH>                  <TH>Title</TH>                </TR>                <xsl:apply-templates select='ROOT'/>              </TABLE>             </BODY>           </HTML>         </xsl:template>     </xsl:stylesheet> 

The code that generates the HTML file by applying the XSLT file Employee.xsl (stored in the C:\PSS2005 directory) follows:

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandText = "SELECT TOP 10 * FROM HumanResources.Employee " +                 "FOR XML AUTO";             cmd.CommandType   = SqlXmlCommandType.Sql;             cmd.RootTag   = "ROOT";             cmd.XslPath   = @"C:\PSS2005\Employee.xsl";             Stream s = cmd.ExecuteStream(  );             byte[] b = new byte[s.Length];             s.Read(b, 0, b.Length);             FileStream fs = new FileStream(@"C:\PSS2005\Employee.html",                 FileMode.OpenOrCreate);             fs.Write(b, 0, b.Length);             fs.Flush(  );         }     } 

The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.Sql from the values described in Table 6-4. This specifies that the SQL command in the CommandText property is processed. The ExecuteStream( ) method of the SqlXmlCommand object returns the results of executing the commandin this case a SQL queryas a Stream object.

The RootTag property of the SqlXmlCommand object specifies the root element for the XML generated by the queryin this case ROOT, which matches the xsl:apply-templates select element in the XSLT transformation file. The XslPath property specifies the full name of the XSLT transformation file to applythe path to the file can be absolute or relative.

An excerpt from the resulting HTML file, named C:\PSS2005\Employee.html, follows:

     <HTML>     <HEAD>     <META http-equiv="Content-Type" content="text/html; charset=utf-8">     </HEAD>     <BODY>     <TABLE>     <TR>     <TH>Employee ID</TH>     <TH>Title</TH>     </TR>     <TR>     <TD>1</TD>     <TD>Production Technician - WC60</TD>     </TR>     <TR>     <TD>2</TD>     <TD>Marketing Assistant</TD>     </TR>     <TR>     ...     <TR>     <TD>10</TD>     <TD>Production Technician - WC10</TD>     </TR>     </TABLE>     </BODY>     </HTML> 

Figure 6-3 shows the file displayed in Internet Explorer.

6.2.3. Annotated Mapping Schemas

An XML schema defines the structure of an XML document and any constraints on the data in the document. In an XML schema, the <xs:schema> element encloses the schema. The <xs:schema> element also contains attributes that define the namespace that the schema is in, as well as namespaces used in the schema. A valid XSD schema is derived from the XML schema namespace at http://www.w3.org/2001/XMLSchema and must have the <xs:schema> element defined as follows:

     <xs:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 

Annotations are attributes used to map XML data to database tables and columns in a relational database, and to specify relationships between and constraints on multiple tables within an XSD schema. These mapping schemas let you do the following:

• Use XML template queries against XML views returned by the XSD schema and return the results as an XML document. Template queries are discussed later in this chapter in the section "XML Template Queries."

  Figure 6-3. Results for applying an XSLT transformation example

  

• Use UpdateGrams to modify data in a SQL Server database. UpdateGrams are discussed later in this chapter in the section "UpdateGrams."

• Bulk load XML data into a SQL Server database. Bulk loading data is discussed in Chapter 7.

Annotations are defined in the urn:schemas-microsoft-com:mapping-schema namespace. Adding this namespace to the <xs:schema> element is the easiest way to specify the namespace:

     <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"                 xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> 

You can specify any prefix (instead of sql) for the namespace.

You can easily create an XSD schema using XML Schema Designer in Visual Studio 2005. For example, to create a schema for the HumanResources.Employee table in AdventureWorks, follow these steps:

1. Create a new a project in Visual Studio 2005.

2. Right-click the project in Solution Explorer and select Add New Item from the context menu to open the Add New Item dialog box.

3. Open the Server Explorer window by selecting View Server Explorer from the main menu.

4. Drag the Employee (HumanResources) table in Server Explorer onto the XML Schema Designer surface Employee.xsd.

5. Right-click the XML Schema Designer surface and select View Code from the context menu to view the XSD schema that follows:

        <?xml version="1.0" encoding="utf-8"?>     <xs:schema        targetNamespace="http://tempuri.org/Employee.xsd" elementFormDefault="qualified"       xmlns="http://tempuri.org/Employee.xsd"       xmlns:mstns="http://tempuri.org/Employee.xsd"       xmlns:xs="http://www.w3.org/2001/XMLSchema"       xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">       <xs:element name="Document">         <xs:complexType>           <xs:choice minOccurs="0" maxOccurs="unbounded">             <xs:element name="Employee">               <xs:complexType>                 <xs:sequence>                   <xs:element name="EmployeeID" type="xs:int" />                   <xs:element name="NationalIDNumber" type="xs:string" />                   <xs:element name="ContactID" type="xs:int" />                   <xs:element name="LoginID" type="xs:string" />                   <xs:element name="ManagerID" type="xs:int" minOccurs="0" />                   <xs:element name="Title" type="xs:string" />                   <xs:element name="BirthDate" type="xs:dateTime" />                   <xs:element name="MaritalStatus" type="xs:string" />                   <xs:element name="Gender" type="xs:string" />                   <xs:element name="HireDate" type="xs:dateTime" />                   <xs:element name="SalariedFlag" type="xs:boolean" />                   <xs:element name="VacationHours" type="xs:short" />                   <xs:element name="SickLeaveHours" type="xs:short" />                   <xs:element name="CurrentFlag" type="xs:boolean" />                   <xs:element name="rowguid" type="xs:string" />                   <xs:element name="ModifiedDate" type="xs:dateTime" />                 </xs:sequence>               </xs:complexType>             </xs:element>           </xs:choice>         </xs:complexType>         <xs:unique name="DocumentKey1">           <xs:selector xpath=".//mstns:Employee" />           <xs:field xpath="mstns:EmployeeID" />         </xs:unique>       </xs:element>     </xs:schema> 

   
   

You can add annotations to this XSD schema and remove schema information that you do not need. The following example shows the same schema with sql:relation and sql:field mappings added and unnecessary information removed. Note the addition of the highlighted mapping-schema namespace in the xs:schema element.

     <xs:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"       xmlns:sql="urn:schemas-microsoft-com:mapping-schema">       <xs:element name="Employee" sql:relation="HumanResources.Employee">         <xs:complexType>           <xs:sequence>             <xs:element name="EmployeeID" type="xs:int" sql:field="EmployeeID" />             <xs:element name="NationalIDNumber" type="xs:string"               sql:field="NationalIDNumber" />             <xs:element name="ContactID" type="xs:int" sql:field="ContactID" />             <xs:element name="LoginID" type="xs:string" sql:field="LoginID" />             <xs:element name="ManagerID" type="xs:int" sql:field="ManagerID" />             <xs:element name="Title" type="xs:string" sql:field="Title" />             <xs:element name="BirthDate" type="xs:date" sql:field="BirthDate" />             <xs:element name="MaritalStatus" type="xs:string"               sql:field="MaritalStatus" />             <xs:element name="Gender" type="xs:string" sql:field="Gender" />             <xs:element name="HireDate" type="xs:date" sql:field="HireDate" />             <xs:element name="SalariedFlag" type="xs:boolean"               sql:field="SalariedFlag" />             <xs:element name="VacationHours" type="xs:int"               sql:field="VacationHours" />             <xs:element name="SickLeaveHours" type="xs:int"               sql:field="SickLeaveHours" />             <xs:element name="CurrentFlag" type="xs:boolean"               sql:field="CurrentFlag" />             <xs:element name="rowguid" type="xs:string" sql:field="rowguid" />             <xs:element name="ModifiedDate" type="xs:date"               sql:field="ModifiedDate" />           </xs:sequence>         </xs:complexType>       </xs:element>     </xs:schema> 

If the sql:field attribute is not specified for a field element or attribute, that element is automatically mapped to a column that has the same name, if one exists. The annotations in the example are actually unnecessary, because field elements and columns all have the same name.

The case-sensitivity of the table and field names defined by the sql:relation and sql:field attributes is determined by whether SQL Server is using case-sensitive collation.

Table 6-7 describes the most common mapping schema annotations.

Table 6-7. SQL Server 2005 mapping schema annotations

Annotation	Description
sql:encode	Specifies that an element or attribute mapped to a BLOB-type column is returned as a URL to the field rather than as Base64-encoded data within the XML document.
sql:field	Maps an element to a field in the table specified by the sql:relation attribute for the parent element.
sql:guid	Specifies that a GUID-type column value is used in the UpdateGram for that column rather than the value provided in the UpdateGram.
sql:hide	Hides an element or attribute specified in the schema in the resulting XML document.
sql:identity	Specifies for an identity-type column how the column is updated using an UpdateGram.
sql:inverse	Specifies that the UpdateGram should invert the update order of the parent-child relationship specified in a sql:relationship element. This overcomes primary key/foreign key violations when used with an UpdateGram or bulk load operation.
sql:is-constant	Specifies an element that does not map to a column in a table but is included in the XML document output.
sql:key-fields	Specifies columns that uniquely identify rows within a table.
sql:limit-field	Specifies that an attribute or element contains a limiting value specified using the sql:limit-value annotation.
sql:limit-value	Specifies the limit value in the column specified by the sql:limit-field annotation.
sql:mapped	Specifies whether elements are mapped to a table or column and whether they appear in the XML document output. The annotation takes the value 0 for false or 1 for true.
sql:max-depth	Specifies maximum depth in recursive relationships specified in the schema.
sql:overflow-field	Specifies the column that contains overflow dataunconsumed data from the source XML column.
sql:prefix	Prepends ID, IDREF, and IDREFS with a string prefix to ensure uniqueness within an XML document.
sql:relation	Maps an XML item to the specified table in the database.
sql:relationship	Specifies a relationship for a table (sql:relation) element using parent, child, parent-key, and child-key attributes to define the relationship.

Annotations are used to define the relational hierarchy of the data based on the relationships of the underlying tables. Specify a <sql:relationship> element for each relationship in the <xs:appinfo> element within the <xs:annotation> element in the XSD schema. The following code snippet defines a parent-child relationship between the Sales.Customer and Sales.SalesOrderHeader tables in AdventureWorks:

     <xsd:annotation>       <xsd:appinfo>         <sql:relationship name="Customer-SalesOrderHeader"           parent="Sales.Customer"           parent-key="CustomerID"           child="Sales.SalesOrderHeader"           child-key="CustomerID" />       </xsd:appinfo>     </xsd:annotation> 

Add the <xsd:annotation> element immediately following the <xs:schema> element.

Table 6-8 describes annotation attributes used to define an XSD relationship.

Table 6-8. sql:relationship annotation attributes

sql:relationship annotation attribute	Description
name	Unique name within the XSD schema for the relationship.
parent	Specifies the parent table. This element is optionalthe parent will be inferred from the hierarchy if the parent attribute is missing.
parent-key	Specifies the parent key. Separate field names with a space if the key has more than one field.
child	Specifies the child table.
child-key	Specifies the child key that maps to the parent key. Separate field names with a space if the key has more than one field.

For more information about XSD schemas, see Microsoft SQL Server 2005 Books Online.

6.2.4. XML Template Queries

An XML template query is an XML document with one or more T-SQL or XPath queries inside. An XML template query lets you query an XML document using T-SQL or XPath. The syntax is:

     <rootName xmlns:sql="urn:schemas-microsoft-com:xml-sql" [ sql:xsl="stylesheet" ] >         [ <sql:header>             [ <sql:param name="paramName">paramValue</sql:param> [ ... n ] ]         </sql:header> ]          <sql:query client-side-xml="n">>             tsqlQuery [ ... n ]          </sql:query> [ ... n ]          <sql:xpath-query mapping-schema="annotatedSchemaFile">             xpathQuery          </sql:xpath-query>     </rootname> 

where:

rootName

Specifies the name of the top-level element.

stylesheet

Specifies the name of the XSLT stylesheet to apply to the result set before returning the formatted results to the client. If an XSLT stylesheet is specified, the results are transformed before they are returned to the client as formatted results.

<sql:param name= "paramName">paramValue</sql:param>

Specifies optional parameter values for parameterized T-SQL queries and stored procedures.

tsqlQuery

Specifies one or more T-SQL statements making up a batch. You can also specify one or more <sql:query> elements (i.e., one or more T-SQL statement batches).

annotatedSchemaFile

Specifies a reference to an annotated XSD schema. Annotated XML-Data Reduced (XDR) schemas are also supported for backward compatibility.

xpathQuery

Specifies an XPath query. A discussion about XPath queries is beyond the scope of this book. For information about XPath queries, see XPath and XPointer (O'Reilly, 2002) or the W3C XPath Specification at http://www.w3.org/TR/1999/PR-xpath-19991008.html.

The following example uses an XML template file containing a T-SQL query to return an XML document containing data about the top two employees in the HumanResources.Employee table in AdventureWorks. The XML template containing the T-SQL query follows:

     <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">       <sql:query>         SELECT TOP 2 * FROM HumanResources.Employee FOR XML AUTO       </sql:query>     </ROOT> 

The code that executes the query in the XML template file (stored in the C:\PSS2005 directory) follows:

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandType = SqlXmlCommandType.TemplateFile;             cmd.CommandText = @"C:\PSS2005\TopTwoEmployeesTemplate.xml";             Stream stream = cmd.ExecuteStream(  );             StreamReader sr = new StreamReader(stream);             string s = sr.ReadToEnd(  );             Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

The results follow:

     <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">       <HumanResources.Employee Employee NationalIDNumber="14417807"         Contact Login Manager         Title="Production Technician - WC60" BirthDate="1972-05-15T00:00:00"         MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00" SalariedFlag="0"         VacationHours="21" SickLeaveHours="30" CurrentFlag="1"         rowgu         ModifiedDate="2004-07-31T00:00:00"/>       <HumanResources.Employee Employee NationalIDNumber="253022876"         Contact Login Manager         Title="Marketing Assistant" BirthDate="1977-06-03T00:00:00"         MaritalStatus="S" Gender="M" HireDate="1997-02-26T00:00:00" SalariedFlag="0"         VacationHours="42" SickLeaveHours="41" CurrentFlag="1"         rowgu         ModifiedDate="2004-07-31T00:00:00"/>     </ROOT> 

The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.TemplateFile from the values described in Table 6-4. This specifies that the template file at the location specified by the CommandText property is executed. The ExecuteStream( ) method of the SqlXmlCommand object returns the results of executing the commandin this case a query in an XML template fileas a Stream object.

The next example shows how to execute an XPath query against a mapping schema for the HumanResources.Employee table in the AdventureWorks database. The mapping schema follows:

     <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"       xmlns:sql="urn:schemas-microsoft-com:mapping-schema">       <xsd:element name="Employee" sql:relation="HumanResources.Employee">         <xsd:complexType>           <xsd:sequence>             <xsd:element name="EmployeeID" type="xsd:int" sql:field="EmployeeID" />             <xsd:element name="NationalIDNumber" type="xsd:string"               sql:field="NationalIDNumber" />             <xsd:element name="ContactID" type="xsd:int" sql:field="ContactID" />             <xsd:element name="LoginID" type="xsd:string" sql:field="LoginID" />             <xsd:element name="ManagerID" type="xsd:int" sql:field="ManagerID" />             <xsd:element name="Title" type="xsd:string" sql:field="Title" />             <xsd:element name="BirthDate" type="xsd:date" sql:field="BirthDate" />             <xsd:element name="MaritalStatus" type="xsd:string"               sql:field="MaritalStatus" />             <xsd:element name="Gender" type="xsd:string" sql:field="Gender" />             <xsd:element name="HireDate" type="xsd:date" sql:field="HireDate" />             <xsd:element name="SalariedFlag" type="xsd:boolean"               sql:field="SalariedFlag" />             <xsd:element name="VacationHours" type="xsd:int"               sql:field="VacationHours" />             <xsd:element name="SickLeaveHours" type="xsd:int"               sql:field="SickLeaveHours" />             <xsd:element name="CurrentFlag" type="xsd:boolean"               sql:field="CurrentFlag" />             <xsd:element name="rowguid" type="xsd:string" sql:field="rowguid" />             <xsd:element name="ModifiedDate" type="xsd:date"               sql:field="ModifiedDate" />           </xsd:sequence>         </xsd:complexType>       </xsd:element>     </xsd:schema> 

The code that returns an XML document containing data for the employee with EmployeeID = 2 uses an XPath query and the preceding mapping file (stored in the C:\PSS2005 directory):

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=AdventureWorks;Integrated Security=SSPI");             cmd.CommandText = @"Employee[EmployeeID=2]";             cmd.CommandType = SqlXmlCommandType.XPath;             cmd.RootTag = "ROOT";             cmd.SchemaPath = @"C:\PSS2005\Employee.xsd";             Stream stream = cmd.ExecuteStream(  );             StreamReader sr = new StreamReader(stream);             string s = sr.ReadToEnd(  );             Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

The results follow:

     <?xml version="1.0" encoding="utf-8" ?>     <ROOT>       <Employee>         <EmployeeID>2</EmployeeID>         <NationalIDNumber>253022876</NationalIDNumber>         <ContactID>1030</ContactID>         <LoginID>adventure-works\kevin0</LoginID>         <ManagerID>6</ManagerID>         <Title>Marketing Assistant</Title>         <BirthDate>1977-06-03</BirthDate>         <MaritalStatus>S</MaritalStatus>         <Gender>M</Gender>         <HireDate>1997-02-26</HireDate>         <SalariedFlag>0</SalariedFlag>         <VacationHours>42</VacationHours>         <SickLeaveHours>41</SickLeaveHours>         <CurrentFlag>1</CurrentFlag>         <rowguid>1B480240-95C0-410F-A717-EB29943C8886</rowguid>         <ModifiedDate>2004-07-31</ModifiedDate>       </Employee>     </ROOT> 

The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.Xpath from the values described in Table 6-4. This specifies that the XPath command in the CommandText property is executed. The ExecuteStream( ) method of the SqlXmlCommand object returns the results of executing the commandin this case an XPath queryas a Stream object.

6.2.5. UpdateGrams

An UpdateGram is an XML template used to insert, update, or delete data in the database. An UpdateGram uses mapping information provided in the annotated XML (XSD or XDR) schema. The UpdateGram format is:

     <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">       <updg:sync [ mapping-schema= "AnnotatedSchemaFile.xml" ] >         <updg:before>           ...         </updg:before>         <updg:after>           ...         </updg:after>       </updg:sync>     </ROOT> 

where the following elements are defined in the urn:schemas-microsoft-com:xml-updategram namespace:

<sync>

Contains one or more pairs of <before> and <after> blocks. These blocks must be specified as pairs, even if they are empty. An UpdateGram can contain multiple <sync> blocks, each of which is treated as a transactional unit.

mapping-schema= "AnnotatedSchemaFile.xml"

Optionally specifies an annotated XSD or XDR mapping schema file used to map elements and attributes in the <before> and <after> blocks to tables and columns in the database.

<before>

Contains the original version (before state) of the record instance.

<after>

Contains the updated version (after state) of the record instance.

An UpdateGram uses the operations described in Table 6-9.

Table 6-9. UpdateGram operations

Operation	Description
insert	The record is inserted if it appears only in the <after> block.
update	The record is updated if it appears in the <before> block with a corresponding record in the <after> block. The record is updated to the values specified in the <after> block.
delete	The record is deleted if it appears only in the <before> block.

The UpdateGram mapping to the database can be implicit or explicitly specified using an XSD or XDR schema. Implicit mapping maps each element in the <before> and <after> elements to a table, and each attribute of the <before> and <after> elements to a column in the table.

Explicit mapping uses the annotated schema file specified by the mapping-schema attribute of the <sync> element to map elements and attributes in the <before> and <after> elements to tables and columns in the database. The path of the mapping schema file is specified relative to the location of the UpdateGram.

The examples in this section use a table named UpdateGramTable. Create the table using the following T-SQL statement:

     USE ProgrammingSqlServer2005     CREATE TABLE UpdateGramTable     (       ID int,       Name varchar(50)     ) 

The following code executes an UpdateGram. To process the three UpdateGrams that follow the code, replace the full filename of the UpdateGram file passed into the StreamReader constructor with the name of each of the three UpdateGram files in the order in which they appear.

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             StreamReader sr = new StreamReader(@"C:\PSS2005\UpdateGram.xml");             string s = sr.ReadToEnd(  );             sr.Close(  );             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=ProgrammingSqlServer2005;Integrated Security=SSPI");             cmd.CommandType = SqlXmlCommandType.UpdateGram;             cmd.CommandText = s;             cmd.ExecuteNonQuery(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.UpdateGram from the values described in Table 6-4. This specifies that the UpdateGram in the CommandText property is processed. The ExecuteNonQuery( ) method of the SqlXmlCommand object executes the commandin this case, processes the UpdateGramand returns nothing.

The following UpdateGram inserts three records into the table UpdateGramTable:

     <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">       <updg:sync >         <updg:before>         </updg:before>         <updg:after>           <UpdateGramTable  Name="Record 1"/>           <UpdateGramTable  Name="Record 2"/>           <UpdateGramTable  Name="Record 3"/>         </updg:after>       </updg:sync>     </ROOT> 

The following UpdateGram deletes the record with ID = 2 from the table UpdateGramTable:

     <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">       <updg:sync >         <updg:before>           <UpdateGramTable  Name="Record 2"/>         </updg:before>         <updg:after>         </updg:after>       </updg:sync>     </ROOT> 

The following UpdateGram updates the record with ID = 3 in the table UpdateGramTable:

     <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">       <updg:sync >         <updg:before>           <UpdateGramTable  Name="Record 3"/>         </updg:before>         <updg:after>           <UpdateGramTable  Name="Updated Record 3"/>         </updg:after>       </updg:sync>     </ROOT> 

6.2.6. DiffGrams

A DiffGram is an XML document format introduced with the DataSet in Visual Studio .NET 1.0 and used to synchronize offline changes made to data with a database server or other persistent store using a DataAdapter. The DiffGram format is:

     <?xml version="1.0"?>     <diffgr:diffgram       xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"       xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"       xmlns:xsd="http://www.w3.org/2001/XMLSchema">         <DataInstance>           ...         </DataInstance>         [<diffgr:before>           ...         </diffgr:before>]     </diffgr:diffgram> 

where:

< DataInstance>

Contains the most recent version of all data, including unchanged data and any changes made offline by the client

<before>

Contains the original data instances (records) for data that has been updated or deleted

The DiffGram uses the following XML annotations that are defined in the urn:schemas-microsoft-com:xml-diffgram-v1 namespace:

id

Associates data instance elements in the <DataInstance> and <before> blocks

hasChanges

Specified as inserted or modified on elements in the <DataInstance> block for inserted or updated records

parentID

Specifies parent-child relationships within the <before> block, and is used to determine the order in which updates are processed

A DiffGram identifies whether records are unchanged, deleted, updated, or inserted according to the rules described in Table 6-10.

Table 6-10. DiffGram record status rules

Record Status	Description
unchanged	Element exists in the <DataInstance> block but not in the <before> block.
inserted	Element exists in the <DataInstance> block but not in the <before> block. The element in the <DataInstance> block has the hasChanges attribute set to inserted.
updated	Element exists both in the <DataInstance> block and the <before> block. The records are associated using the id annotation, and the hasChanges attribute is set to modified on the element in the <DataInstance> block.
deleted	Element exists only in the <before> block.

Examples of using a DiffGram from SQLXML 4.0 managed classes to modify data in the database follow. The examples use a table named DiffGramTable. Create the table using the following T-SQL statement:

     USE ProgrammingSqlServer2005     CREATE TABLE DiffGramTable     (       ID int,       Name varchar(50)     ) 

The examples in this section also use an XSD annotated mapping schema named DiffGramTable.xsd for the table DiffGramTable:

     <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"       xmlns:sql="urn:schemas-microsoft-com:mapping-schema">       <xsd:element name="DiffGramTable" sql:relation="DiffGramTable">         <xsd:complexType>           <xsd:sequence>             <xsd:element name="ID" type="xsd:int" sql:field="ID" />             <xsd:element name="Name" type="xsd:string" sql:field="Name" />           </xsd:sequence>         </xsd:complexType>       </xsd:element>     </xsd:schema> 

The following code executes a DiffGram. To process the three DiffGrams that follow the code, replace the full filename of the DiffGram file passed into the highlighted StreamReader constructor with the name of each of the three DiffGram files in the order in which they appear.

     using System;     using System.IO;     using Microsoft.Data.SqlXml;     class Program     {         static void Main(string[] args)         {             StreamReader sr = new StreamReader(@"C:\PSS2005\DiffGram.xml");             string s = sr.ReadToEnd(  );             sr.Close(  );             SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +                 "database=ProgrammingSqlServer2005;Integrated Security=SSPI");             cmd.CommandType = SqlXmlCommandType.DiffGram;             cmd.CommandText = s;             cmd.ExecuteNonQuery(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.DiffGram from the values described in Table 6-4. This specifies that the DiffGram in the CommandText property is processed. The ExecuteNonQuery( ) method of the SqlXmlCommand object executes the commandin this case processes the DiffGramand returns nothing.

The following DiffGram inserts three records into the table DiffGramTable. Note that the hasChanges attribute is set to "inserted".

     <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"       sql:mapping-schema="c:\PSS2005\DiffGramTable.xsd">       <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"         xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">         <DocumentElement>           <DiffGramTable diffgr: msdata:rowOrder="0"             diffgr:hasChanges="inserted">             <ID>1</ID>             <Name>Record 1</Name>           </DiffGramTable>           <DiffGramTable diffgr: msdata:rowOrder="1"             diffgr:hasChanges="inserted">             <ID>2</ID>             <Name>Record 2</Name>           </DiffGramTable>           <DiffGramTable diffgr: msdata:rowOrder="2"             diffgr:hasChanges="inserted">             <ID>3</ID>             <Name>Record 3</Name>           </DiffGramTable>         </DocumentElement>       </diffgr:diffgram>     </ROOT> 

The following DiffGram deletes the record with ID = 2 from the table DiffGramTable. In this case, hasChanges is not used.

     <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"       sql:mapping-schema="c:\PSS2005\DiffGramTable.xsd">       <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"         xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">         <DocumentElement>         </DocumentElement>         <diffgr:before>           <DiffGramTable diffgr: msdata:rowOrder="1">             <ID>2</ID>             <Name>Record 2</Name>           </DiffGramTable>         </diffgr:before>       </diffgr:diffgram>     </ROOT> 

The following UpdateGram updates the record with ID = 3 in the table UpdateGramTable. Here, hasChanges is set to ="modified".

     <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"       sql:mapping-schema="c:\PSS2005\DiffGramTable.xsd">       <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"       xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">         <DocumentElement>           <DiffGramTable diffgr: msdata:rowOrder="1"             diffgr:hasChanges="modified">             <ID>3</ID>             <Name>Updated Record 3</Name>           </DiffGramTable>         </DocumentElement>         <diffgr:before>           <DiffGramTable diffgr: msdata:rowOrder="1">             <ID>3</ID>             <Name>Record 3</Name>           </DiffGramTable>         </diffgr:before>       </diffgr:diffgram>     </ROOT>