Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize
2 out of 2 rated this helpful - Rate this topic

SQLXML Managed Classes

SQL Server 2000
 

Scott Swigart
3 Leaf Solutions

March 2002

Applies to:
    Microsoft® SQL Server™ 2000
    Microsoft Visual Studio® .NET

Summary: How to use SQLXML Managed Classes for .NET code to retrieve XML from a Microsoft SQL Server database or to generate XML on the client side, using ad hoc queries, stored procedures, annotated schemas with XPath, or XML templates. (19 printed pages)

Contents

Introduction
SQLXML Managed Classes Object Model
Using SQLXML Managed Classes
Conclusion
About the Author

Introduction

Companies have found SQLXML to be a useful technology for a number of scenarios. A company may receive XML documents in various formats and wish to store this data in a Microsoft® SQL Server™ 2000 database, or a company may need to serve XML documents in various formats for trading partners. In addition, XML documents may be transformed using style sheets to target various browsers, handheld PCs, or other devices. For these scenarios, SQLXML allows you to send XML data to and retrieve it from SQL Server. This capability means that the developer is not required to author and maintain all the code needed to transform XML into some other format for consumption by the database.

SQLXML Managed Classes allow you to author .NET code that takes advantage of the XML features provided by SQLXML 3.0. You can write managed Microsoft Visual C#™ or Visual Basic® .NET code that utilizes FOR XML, XML templates, annotated schemas, and DiffGrams. This white paper will explain the mechanics of using SQLXML Managed Classes for a variety of scenarios.

This paper is based on SQLXML 3. You can download the latest release of SQLXML.

SQLXML Managed Classes Object Model

Three primary classes are used to access the XML functionality of SQL Server. These are:

  • SqlXmlCommand. Used to send a Transact-SQL statement to the database, execute a stored procedure, or query the database using other technologies (such as annotated schemas and XML templates) and get the results back as XML. This object supports a wide range of options, which are discussed in detail later in this document.
  • SqlXmlParameter. Used to specify the value for a parameter in the command. This can be a parameter to an ad hoc query, stored procedure, XPath query, or XML template.
  • SqlXmlAdapter. Used to populate a DataSet object with an XML result set, or update the database with an XML DiffGram.

Using SQLXML Managed Classes

The SqlXmlCommand class is the primary class used when retrieving data from SQL Server in XML format. This class allows you to send queries to the database and retrieve the results as a stream or XmlReader object, or to send the output directly into another stream. The query can be parameterized, and with the help of the SqlXmlParameter class, you can specify the values for these parameters. You can execute queries using ad hoc Transact-SQL statements, stored procedures, annotated schemas and XPath, and templates. You can have the XML returned directly from the database, or do the conversion to XML on the client side by simply setting a property. Finally, you can have the SqlXmlCommand object automatically apply a style sheet to the XML result set, performing any required transformation.

SqlXmlCommand

This section describes how to use SqlXmlCommand to retrieve XML data from SQL Server.

When authoring managed code, you often use classes from a variety of namespaces. The code in this paper assumes that you have included the following "using" directives:

using System;
using System.Data;
using Microsoft.Data.SqlXml;
using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.IO;
using System.Security.Cryptography;

Regardless of the scenario for retrieving data, one setup for SqlXmlCommand is universal. SqlXmlCommand supports a single constructor that takes an ActiveX Data Objects (ADO) connection string as an argument, as shown below:

static string NorthwindConnString =
  "Provider=SQLOLEDB;Server=(local);database=Northwind;" +
  "Integrated Security=SSPI";
SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);

The code samples in the remainder of this white paper assume that the NorthwindConnString variable has been initialized.

When an XML result set is returned from SQL Server 2000, it is typically an XML fragment, lacking a single root tag. Therefore, it is important to set the RootTag property of SqlXmlCommand so that the resulting XML is well formed, as follows:

cmd.RootTag="products";

SqlXmlCommand.ExecuteStream

The first example of retrieving XML data uses the ExecuteStream method. This method returns the XML data from SQL Server as a simple .NET stream instance:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag="products";
cmd.CommandType = SqlXmlCommandType.Sql;
cmd.CommandText= "SELECT * FROM products FOR XML AUTO";
StreamReader sr = new StreamReader(cmd.ExecuteStream());

One reason for returning the results as a stream might be to perform an operation on the document as a whole without regard for its contents. Such an operation could include compressing or encrypting the result set. In this case, the XML results are not treated as an actual XML document, but rather as a stream of bytes.

You can use the following code to DES encrypt the XML data that was returned from the previous example:

Byte[] key = new Byte[] {1, 50, 80, 111, 4, 255, 18, 217};
Byte[] iv = new Byte[] {12, 240, 193, 38, 193, 2, 58, 19};
DESCryptoServiceProvider Crypto = new DESCryptoServiceProvider();
FileStream FStream = File.Open(@"c:\Encrypted.bin",
  FileMode.Create,
  FileAccess.Write);
CryptoStream EncStream = new CryptoStream(FStream,
  Crypto.CreateEncryptor(key, iv), CryptoStreamMode.Write);
StreamWriter SW = new StreamWriter(EncStream);
SW.Write(sr.ReadToEnd());
SW.Close();

SW.Write(sr.ReadToEnd()); reads the XML input stream and sends the data through CryptoStream and FileStream to store the encrypted XML on disk.

SqlXmlCommand.ExecuteToStream

Rather than return the XML data from SQL Server as a simple Stream object, you may wish to send the XML result set directly to the destination without modification. In this case, you can use the ExecuteToStream method to send the resulting XML directly to a FileStream object, a NetworkStream object, or, in the case of ASP.NET, the Response object. The following code sends the XML results directly to a FileStream object. The Process class is then used to display the results in Internet Explorer (assuming that XML files are associated with Internet Explorer, which is the default configuration).

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag="products";
cmd.CommandType = SqlXmlCommandType.Sql;
cmd.CommandText= "SELECT * FROM products FOR XML AUTO";
FileStream f = new FileStream(@"c:\products.xml",FileMode.Create);
cmd.ExecuteToStream(f);
f.Close();
System.Diagnostics.Process.Start(@"c:\products.xml");

This code retrieves all the rows and columns from the products table and displays the results as follows:

<?xml version="1.0" encoding="utf-8" ?>
<Orders>
  <Order EmployeeID="5" CustomerID="VINET" OrderID="10248">
    <OrderDate>1996-07-04T00:00:00</OrderDate>
    <RequiredDate>1996-08-01T00:00:00</RequiredDate>
    <ShippedDate>1996-07-16T00:00:00</ShippedDate>
    <ShipVia>3</ShipVia>
    <Freight>32.38</Freight>
    <ShipName>Vins et alcools Chevalier</ShipName>
    <ShipAddress>59 rue de l&apos;Abbaye</ShipAddress>
    <ShipCity>Reims</ShipCity>
    <ShipPostalCode>51100</ShipPostalCode>
    <ShipCountry>France</ShipCountry>
  </Order>
  <Order EmployeeID="6" CustomerID="TOMSP" OrderID="10249">
    <OrderDate>1996-07-05T00:00:00</OrderDate>
    <RequiredDate>1996-08-16T00:00:00</RequiredDate>
    <ShippedDate>1996-07-10T00:00:00</ShippedDate>
    <ShipVia>1</ShipVia>
    <Freight>11.61</Freight>
    <ShipName>Toms Spezialitüten</ShipName>
    <ShipAddress>Luisenstr. 48</ShipAddress>
    <ShipCity>Münster</ShipCity>
    <ShipPostalCode>44087</ShipPostalCode>
    <ShipCountry>Germany</ShipCountry>
  </Order>...
</Orders>

SqlXmlCommand.ExecuteXmlReader

Typically you retrieve XML results because you want to work data in an XML format. You might retrieve data as XML so that you can render output for multiple devices; you might want to send XML data to trading partners in various formats; or you might be emitting XML so that it can be consumed by an XML-aware application. For these kinds of scenarios, you should use the ExecuteXmlReader method, which returns an XmlReader object. This is a high-performance object that lets you iterate through the nodes in the XML result set. If you need random access to the XML results, you can pass XmlReader as an argument to the constructor of an XmlDocument object. The XmlDocument object gives you full Document Object Model (DOM) Level 1 and Level 2 Core support for manipulating the result set.

The following sample retrieves the results as an XmlReader object and passes it to the constructor of an XmlDocument object. Once the data is in an XmlDocument object, multiple XPath queries can extract results without going back to the database. In this case, products with a unit price greater than 20 are extracted.

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag="products";
cmd.CommandType = SqlXmlCommandType.Sql;
cmd.CommandText= "SELECT * FROM products FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
XmlNodeList xnl = xd.SelectNodes("//products[@UnitPrice > 20]");
foreach(XmlNode xn in xnl)
{
  Console.WriteLine(xn.OuterXml);
}

Up to this point, all the examples have used ad hoc queries to retrieve XML from SQL Server. You can also execute stored procedures that return XML. Consider the following stored procedure:

CREATE PROCEDURE dbo.GetEmployeesXml
AS
  SELECT FirstName, LastName FROM employees FOR XML AUTO
  RETURN

In this case, the FOR XML AUTO clause is part of the SELECT statement inside the procedure body. This statement can be executed as follows:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Employees";
cmd.CommandText = "EXEC GetEmployeesXML";
XmlReader xr = cmd.ExecuteXmlReader();

SqlXmlCommand.ClientSideXml

In many cases, you cannot edit existing stored procedures to return an XML result set. For example, consider the following stored procedure:

ALTER PROCEDURE dbo.GetEmployees
AS
  SELECT FirstName, LastName FROM employees
  RETURN

This is a typical stored procedure that does not return XML. In addition, there are many circumstances where, for performance or other reasons, you want to have the XML generated on the client rather than directly from the database. For these scenarios, you can use the ClientSideXml property of the SqlXmlCommand object, as shown below:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Employees";
cmd.ClientSideXml = true;
cmd.CommandText = "EXEC GetEmployees FOR XML NESTED";
XmlReader xr = cmd.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Console.WriteLine(xd.OuterXml);

To generate XML on the client side involves two important steps:

  • You must set the ClientSideXml property of the SqlXmlCommand object to true.
  • The CommandText property must contain either "FOR XML NESTED" or "FOR XML RAW" or "FOR XML Explicit". This clause is intercepted by the OLE DB provider, and indicates that the result set should be converted to XML.

Even though the stored procedure has not been modified, you end up with an XmlReader object on the client.

This mechanism can also be used for ad hoc queries as shown below:

cmd.ClientSideXml = true;
cmd.CommandText = "SELECT * FROM products FOR XML NESTED";
XmlReader xr = cmd.ExecuteXmlReader();

Annotated schemas

If you are using ad hoc queries or stored procedures to query SQL Server 2000, the columns in the result set are serialized as attributed in the resulting XML document. There is also an element-centric mode for returning the XML using NESTED, AUTO or RAW XML modes, you can do Select * from Products FOR XML NESTED, ELEMENTS.

If you want to change the format of the returned XML, you can use an annotated schema to define which columns will be expressed as elements, and which columns will be expressed as attributes. Annotated schemas let you further modify the result set using standard XPath. An annotated schema is an XML schema document that specifies both the tables and columns that you wish to query, and the structure of the resulting XML. SQLXML 2.0 supported two versions of the schemas, XML Data Reduced (XDR) and W3 XML Schema Definition (XSD). This schema syntax predated the World Wide Web Consortium (W3) release of a schema recommendation. SQLXML 3.0 fully supports the current W3 XML Schema Definition (XSD).

Consider the following schema:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xs:element name="Order" sql:relation="Orders">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="OrderDate" type="xs:dateTime"  />
        <xs:element name="ShipName" type="xs:string"  />
      </xs:sequence>
      <xs:attribute name="OrderID" type="xs:int" />
      <xs:attribute name="CustomerID" type="xs:string" />
      <xs:attribute name="EmployeeID" type="xs:int" />
    </xs:complexType>
  </xs:element>
</xs:schema>

This uses the standard W3 schema syntax with a single exception. The "sql" namespace contains extensions that let you map the components of the schema to specific tables and columns. In this case, the XML element names are identical to the database column names, so the only specific relationship that needs to be established is that of the Order element to the Orders table. This schema can be invoked with the following code:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Orders";
cmd.CommandText = "Order";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = @"..\..\Orders1.xsd";
FileStream f = new FileStream(@"c:\products.xml",FileMode.Create);
cmd.ExecuteToStream(f);
f.Close();
System.Diagnostics.Process.Start(@"c:\products.xml");

In this example, the SchemaPath property specifies a physical path to a schema file. This path is relative to the location of the executable. If you are using a Microsoft Visual Studio® .NET Console application, and the schema is part of your project, the schema is two directories above the executable (which is in a bin\Debug or bin\Release directory). Therefore, the path includes "  \  \" to locate the schema relative to the executable. You can also use an absolute path, or place the schema in the same physical directory as the executable, in which case you would just give the file name, Orders1.xsd.

The schema file is used to map the resulting XML to tables and columns in the database. The CommandText property specifies an XPath query that defines the XML result set. In other words, the schema may specify that all orders be returned, but CommandText may limit the result to orders that match a specific criterion. In the current example, the following XML is returned:

<?xml version="1.0" encoding="utf-8" ?>
<Orders>
  <Order EmployeeID="5" CustomerID="VINET" OrderID="10248">
    <OrderDate>1996-07-04T00:00:00</OrderDate>
    <ShipName>Vins et alcools Chevalier</ShipName>
  </Order>
  <Order EmployeeID="6" CustomerID="TOMSP" OrderID="10249">
    <OrderDate>1996-07-05T00:00:00</OrderDate>
    <ShipName>Toms Spezialitüten</ShipName>
  </Order>
...
</Orders>

Also notice that the annotated schema makes it easy to return some columns as elements and other columns as attributes.

Using Visual Studio .NET to generate annotated schemas

To use Visual Studio .NET to simplify the process of generating annotated schemas, follow these steps:

  1. Open an existing Visual Studio .NET project, and on the Project menu, click Add New Item.
  2. In the Add New Item dialog, in the templates pane, select XML Schema, and click Open.
  3. On the View menu, click Server Explorer (or press CTRL+ALT+S) to open Server Explorer.
  4. Expand Servers, <Machine Name>, SQL Servers, <Machine Name>, Northwind, and Tables.
  5. Drag and drop the Products table on to the design surface.
  6. At the lower-left corner of the schema designer, click the XML tab. You should see the following code:
    <?xml version="1.0" encoding="utf-8" ?>
    <xs:schema id="XMLSchema1" 
       targetNamespace="http://tempuri.org/XMLSchema1.xsd" 
          elementFormDefault="qualified" 
             xmlns="http://tempuri.org/XMLSchema1.xsd" 
                xmlns:mstns="http://tempuri.org/XMLSchema1.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 maxOccurs="unbounded">
            <xs:element name="Products">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="ProductID" msdata:ReadOnly="true"
                    msdata:AutoIncrement="true" type="xs:int" />
                  <xs:element name="ProductName" type="xs:string" />
                  <xs:element name="SupplierID" type="xs:int"
                    minOccurs="0" />
                  <xs:element name="CategoryID" type="xs:int"
                    minOccurs="0" />
                  <xs:element name="QuantityPerUnit" type="xs:string"
                    minOccurs="0" />
                  <xs:element name="UnitPrice" type="xs:decimal"
                    minOccurs="0" />
                  <xs:element name="UnitsInStock" type="xs:short"
                    minOccurs="0" />
                  <xs:element name="UnitsOnOrder" type="xs:short"
                    minOccurs="0" />
                  <xs:element name="ReorderLevel" type="xs:short"
                    minOccurs="0" />
                  <xs:element name="Discontinued" type="xs:boolean" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:choice>
        </xs:complexType>
        <xs:unique name="DocumentKey1" msdata:PrimaryKey="true">
          <xs:selector xpath=".//mstns:Products" />
          <xs:field xpath="mstns:ProductID" />
        </xs:unique>
      </xs:element>
    </xs:schema>
    
  7. Delete the text shown in bold above. The final schema should appear as follows:
    <?xml version="1.0" encoding="utf-8" ?>
    <xs:schema id="XMLSchema1" elementFormDefault="qualified" 
      xmlns="http://tempuri.org/XMLSchema1.xsd"
      xmlns:mstns="http://tempuri.org/XMLSchema1.xsd"
      xmlns:xs="http://www.w3.org/2001/XMLSchema"
      xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
      <xs:element name="Products">
        <xs:complexType>
          <xs:sequence>
            <xs:element name="ProductID" msdata:ReadOnly="true"
              msdata:AutoIncrement="true" type="xs:int" />
            <xs:element name="ProductName" type="xs:string" />
            <xs:element name="SupplierID" type="xs:int" minOccurs="0" />
            <xs:element name="CategoryID" type="xs:int" minOccurs="0" />
            <xs:element name="QuantityPerUnit" type="xs:string" 
              minOccurs="0" />
            <xs:element name="UnitPrice" type="xs:decimal" minOccurs="0" />
            <xs:element name="UnitsInStock" type="xs:short"
              minOccurs="0" />
            <xs:element name="UnitsOnOrder" type="xs:short"
              minOccurs="0" />
            <xs:element name="ReorderLevel" type="xs:short"
              minOccurs="0" />
            <xs:element name="Discontinued" type="xs:boolean" />
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>
    

You can use the following code to query SQL Server using this schema and display the results:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "ProductList";
cmd.CommandText = "Products";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = @"..\..\XMLSchema1.xsd";
FileStream f = new FileStream(@"c:\products.xml",FileMode.Create);
cmd.ExecuteToStream(f);
f.Close();
System.Diagnostics.Process.Start(@"c:\products.xml");

As you can see, Visual Studio takes most of the work out of authoring annotated schemas.

Querying with XPath

The standard XPath syntax allows you to search for matching elements and limit a result set. XPath is traditionally used in conjunction with XSL Transformations (XSLT) to select certain nodes from an input XML document for transformation or rendering. XPath is also used with DOM to select certain nodes to work with programmatically.

SQLXML 3.0 lets you use XPath with annotated schemas to limit the result set.

Consider the following example:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Orders";
cmd.CommandText = "Order[ShipCountry = 'Brazil']";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = @"..\..\Orders_Details.xsd";
FileStream f = new FileStream(@"c:\products.xml",FileMode.Create);
cmd.ExecuteToStream(f);
f.Close();
System.Diagnostics.Process.Start(@"c:\products.xml");

This example retrieves all the orders that were shipped to Brazil. This retrieval is done efficiently by converting the XPath criteria to a Transact-SQL WHERE clause as shown in the following trace:

SELECT 1 AS TAG,0 AS parent,CONVERT(nvarchar(4000),_Q1._TBEZF2,126) AS
[Order!1!OrderDate!element],CONVERT(nvarchar(4000),_Q1._TBMQG2,126) AS
[Order!1!RequiredDate!element],CONVERT(nvarchar(4000),_Q1._TBFZE2,126) AS
[Order!1!ShippedDate!element],_Q1._TBKWC2 AS
[Order!1!ShipVia!element],_Q1._TBB3F2 AS
[Order!1!Freight!element],_Q1._TBGLS1 AS
[Order!1!ShipName!element],_Q1._TBGZD2 AS
[Order!1!ShipAddress!element],_Q1._TBC5C2 AS
[Order!1!ShipCity!element],_Q1._TBCRUW AS
[Order!1!ShipRegion!element],_Q1._TBORD2 AS
[Order!1!ShipPostalCode!element],_Q1._TBNES1 AS
[Order!1!ShipCountry!element],_Q1.A0 AS [Order!1!EmployeeID],_Q1.A1 AS
[Order!1!CustomerID],_Q1.A2 AS [Order!1!OrderID] from (SELECT
_QB0.EmployeeID AS A0,_QB0.CustomerID AS A1,_QB0.OrderID AS
A2,_QB0.OrderID AS _TBPMH2,_QB0.CustomerID AS _TBMRF2,_QB0.EmployeeID AS
_TBHXE2,_QB0.ShipCountry AS _TBNES1,_QB0.ShipPostalCode AS
_TBORD2,_QB0.ShipRegion AS _TBCRUW,_QB0.ShipCity AS
_TBC5C2,_QB0.ShipAddress AS _TBGZD2,_QB0.ShipName AS _TBGLS1,
_QB0.Freight AS _TBB3F2,_QB0.ShipVia AS _TBKWC2,_QB0.ShippedDate AS
_TBFZE2,_QB0.RequiredDate AS _TBMQG2,_QB0.OrderDate AS _TBEZF2 from Orders
_QB0) _Q1 WHERE CONVERT(nvarchar(4000),_Q1._TBNES1,126) IS NOT NULL AND
(CONVERT(nvarchar(4000),_Q1._TBNES1,126) = N'Brazil') FOR XML EXPLICIT,
BINARY BASE64

The details of this SELECT statement are beyond the scope of this paper, but notice that the WHERE clause limits the result set to orders from Brazil, rather than returning all the orders before filtering.

Querying with template files

SQLXML supports exposing SQL Server 2000 directly to the Web using template files that define the query and parameters. These are XML files that are placed in a virtual root and are accessible through HTTP. You can, however, execute these template files directly using SQLXML Managed Classes. You set the CommandText property of the SqlXmlConnection to contain the path to the XML template file, and then you set the CommandType property to "SqlXmlCommandType.TemplateFile". The following example template assumes that you have saved the template as products.xml:

<?xml version="1.0" encoding="utf-8" ?>
<Products xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:query>
  SELECT Products.ProductID, Products.ProductName,
    Suppliers.CompanyName AS SupplierName, Categories.CategoryName,
    Products.QuantityPerUnit, Products.UnitPrice,
    Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel,
    Products.Discontinued
  FROM Products
    INNER JOIN
      Suppliers ON Products.SupplierID = Suppliers.SupplierID
    INNER JOIN
     Categories ON Products.CategoryID = Categories.CategoryID
  FOR XML RAW
  </sql:query>
</Products>

You can then use the following section of code to execute this template and display the results:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Products";
cmd.CommandText = @"..\..\Products.xml";
cmd.CommandType = SqlXmlCommandType.TemplateFile;
FileStream f = new FileStream(@"c:\products.xml",FileMode.Create);
cmd.ExecuteToStream(f);
f.Close();
System.Diagnostics.Process.Start(@"c:\products.xml");

Authoring templates using Visual Studio .NET Query Builder

Although Visual Studio .NET doesn't directly support authoring XML templates, you can get most of the work done using Query Builder, as outlined in the following steps:

  1. With a project open in Visual Studio .NET, on the Project menu, click Add New Item (or press CTRL+SHIFT+A).
  2. In the Add New Item dialog, in the templates pane, select XML File.
  3. In the Name field, enter orders.xml, and click Open.
  4. Add the following code to the file:
    <Products xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:query>
      </sql:query>
    </Products>
    
  5. On the View menu, click Server Explorer (or press CTRL+ALT+S) to open Server Explorer.
  6. Expand Servers, <Machine Name>, SQL Servers, <Machine Name>, and Northwind.
  7. Right-click Views and then click New View. The Query Builder utility opens.
  8. In the Add Table dialog, double-click the following tables: Orders, Order Details, Products, Employees, and Customers.
  9. Click Close.
  10. In the diagram pane, in the Orders table, select the following columns: OrderID, OrderDate, RequiredDate, and ShippedDate.
  11. In the Order Details table, select the following columns: UnitPrice, Quantity, and Discount.
  12. In the Employees table, select the following columns: FirstName and LastName.
  13. In the Customers table, select the following column: CompanyName.
  14. In the Products table, select the following column: ProductName.
  15. Select everything in the SQL pane (SELECT dbo.Orders.OrderID…), and press CTRL+C to copy the selection to the Clipboard.
  16. Close Query Builder.
  17. When prompted to save changes, click No.
  18. In orders.xml, place the insertion point between <sql:query> and </sql:query>.
  19. Press CTRL+V to paste the Clipboard contents into the SQL field.
  20. Just before </sql:query>, type "FOR XML RAW". Press ENTER. The file should appear as follows:
    <?xml version="1.0" encoding="utf-8" ?>
    <Products xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:query>
        SELECT dbo.Orders.OrderID, dbo.Orders.OrderDate,
          dbo.Orders.RequiredDate, dbo.Orders.ShippedDate,
          dbo.[Order Details].UnitPrice,
          dbo.[Order Details].Quantity, dbo.[Order Details].Discount,
          dbo.Employees.LastName, dbo.Employees.FirstName,
          dbo.Customers.CompanyName,
                dbo.Products.ProductName
        FROM dbo.Orders
        INNER JOIN
          dbo.[Order Details] ON dbo.Orders.OrderID =
            dbo.[Order Details].OrderID
        INNER JOIN
          dbo.Employees ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID
        INNER JOIN
          dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID
        INNER JOIN
          dbo.Products ON dbo.[Order Details].ProductID =
            dbo.Products.ProductID
        FOR XML RAW
      </sql:query>
    </Products>
    
  21. You can test this template with the following code:
    SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
    cmd.RootTag = "Orders";
    cmd.CommandText = @"..\..\orders.xml";
    cmd.CommandType = SqlXmlCommandType.TemplateFile;
    FileStream f = new FileStream(@"c:\orders.xml",FileMode.Create);
    cmd.ExecuteToStream(f);
    f.Close();
    System.Diagnostics.Process.Start(@"c:\orders.xml");
    

SqlXmlParameter

Up to this point, all the queries, stored procedures, schemas, XPath expressions, and templates have been static. Often, however, you want to modify the query based on some criteria. For example, you know that you want to select orders by customer, but it isn't until run time that you know the specific customers whose orders you want to see. To make these queries dynamic, you can parameterize them and use the SqlXmlParameter object to specify values for the parameters at run time.

Parameterized ad hoc queries

For an ad hoc query, simply use a "?" to indicate that the value is supplied by a parameter, as shown in the following example:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag="products";
cmd.CommandType = SqlXmlCommandType.Sql;
cmd.CommandText= "SELECT * FROM products WHERE categoryid = ? " +
  "FOR XML AUTO";
SqlXmlParameter p = cmd.CreateParameter();
p.Value = "1";
XmlReader xr = cmd.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Console.WriteLine(xd.OuterXml);

Stored procedure parameters

The model for passing parameters to stored procedures is very similar to the model for parameterized ad hoc queries. You use a "?" for each argument passed to the procedure, and use SqlXmlParameter to assign a value. If the stored procedure does not emit XML (by internally using FOR XML RAW, FOR XML AUTO, or FOR XML EXPLICIT), you must use client-side XML, as illustrated below:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Orders";
cmd.ClientSideXml = true;
cmd.CommandText = "EXEC GetOrders ? FOR XML NESTED";
SqlXmlParameter p = cmd.CreateParameter();
p.Value = "alfki";
XmlReader xr = cmd.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Console.WriteLine(xd.OuterXml);
Note   For ad hoc queries and stored procedures, you can use positional parameters as shown above, but you cannot use named parameters, as you can with XPath and XML templates, which are discussed below.

XPath parameters

You can also parameterize XPath queries. You use the standard XPath variable syntax to denote the position of the parameter in the query; you then can use positional or named parameters to specify the values. In the following example, the CommandText property contains an XPath statement that selects all the order details for a specific product ID. The product ID is specified by an XPath variable called $ProductID ($VariableName is the standard XPath syntax for a variable). You can set the value for this variable using the SqlXmlParameter class, as shown below:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Orders";
cmd.CommandText = "Order/OrderDetails[ProductID = $ProductID]";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = @"..\..\Orders_Details.xsd";
SqlXmlParameter p = cmd.CreateParameter();
p.Name = "@ProductID";
p.Value = "1";
FileStream f = new FileStream(@"c:\products.xml",FileMode.Create);
cmd.ExecuteToStream(f);
f.Close();
System.Diagnostics.Process.Start(@"c:\products.xml");

Template parameters

It is also possible to pass parameters to XML template files. First, the template file must be written to accept parameters. Parameters are specified in the header section of the XML template, as in the following example:

<?xml version="1.0" encoding="utf-8" ?>
<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
  <sql:header >
     <sql:param name='CategoryID'></sql:param>
  </sql:header>
  <sql:query >
      SELECT * FROM Products WHERE CategoryID = @CategoryID FOR XML AUTO
  </sql:query>
</ROOT>

The value for CategoryID can then be specified using the SqlXmlParameter object, as follows:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Orders";
cmd.CommandText = @"..\..\products2.xml";
cmd.CommandType = SqlXmlCommandType.TemplateFile;
SqlXmlParameter p = cmd.CreateParameter();
p.Name = "@CategoryID";
p.Value = "3";
FileStream f = new FileStream(@"c:\orders.xml",FileMode.Create);
cmd.ExecuteToStream(f);
f.Close();
System.Diagnostics.Process.Start(@"c:\orders.xml");

SqlXmlAdapter

The SqlXmlAdapter object can be used to update the database with an XML DiffGram. This is useful if you have generated XML data using an annotated schema, the user has modified the data, and you wish to store the changes back in the database. DiffGrams support insert, update, and delete operations. The SqlXmlAdapter object contains two methods, which are described in the following table.

MethodDescription
Fill(DataSet ds)Populates a DataSet object with XML data retrieved from SQL Server.
Update(DataSet ds)Updates SQL Server to reflect changes to the DataSet object using an XML DiffGram.

For example, assume that you have created the following schema:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xs:element name="Categories">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="CategoryID" type="xs:integer"/>
        <xs:element name="CategoryName" type="xs:string"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

You can use this schema in combination with SqlXmlAdapter to populate a DataSet object and update the database with changes, as shown below:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "ROOT";
cmd.CommandText = "Categories";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = @"..\..\Categories.xsd";
DataSet ds = new DataSet();
SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
ad.Fill(ds);
ds.Tables["Categories"].Rows[0]["CategoryName"] = "Drinks";
ad.Update(ds);

This example populates a DataSet object using an annotated schema. A cell in the DataSet object is changed, and the changes are pushed back to the database using the Update method of the SqlXmlAdapter object.

Conclusion

SQLXML Managed Classes let you author .NET code that takes advantage of the features previously provided by SQLXML. These features include the ability to generate XML on the server or client side using the following mechanisms: ad hoc queries, stored procedures, annotated schemas and XPath, and XML templates.

About the Author

Scott Swigart is a senior principal of 3 Leaf Solutions, a company that specializes in training and consulting services for early adopters of Microsoft technologies. In this role, Scott has provided training and consulting to Microsoft Consulting Services and other corporations since the Beta 1 release of Microsoft .NET. You can find more information about Scott Swigart and 3 Leaf Solutions at www.3leaf.com.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.