SQL Server 2000 and XML

This chapter is from the book

XML and ASP.NET

Learn More Buy

This chapter is from the book

This chapter is from the book 

XML and ASP.NET

Learn More Buy

SQL Server 2000 has several key features right out of the box that enable support for XML. Combining these features with ADO.NET and the XML Framework classes provide a broad range of options for working with data, which simplifies data access over the web.

In addition to the XML support that an out-of-the-box installation of SQL Server provides, Microsoft has also released SQLXML 3.0, a service pack to SQL Server that provides additional features to those already present in SQL Server 2000. The installation executable can be obtained from http://www.microsoft.com/sqlserver.

The first portion of this chapter deals with XML support in a base installment of SQL Server 2000. The additional features SQLXML 3.0 adds is covered later in this chapter.

SQL Server 2000 XML Support

As you will see throughout this chapter, SQL Server 2000 provides several important options for reading XML data from SQL Server. You can explicitly query the database and request XML data back using the FOR XML clause. You can also query SQL Server over HTTP, which enables you to create XML views of relational data that can be queried by using XPath syntax.

SQL Server 2000 added a set of reserved words that extract data with an XML representation. These reserved words are associated with the FOR XML and OPENXML clauses.

FOR XML

Suppose that you want to represent all the rows in the Customers table in the Northwind database as XML. What is the best approach to solving this problem? One way to do this is to fill an ADO.NET DataSet object, loop through the rows and columns of its Table object, and add each column to an XmlDocument object. You have already seen better ways to handle this problem because the DataSet object provides rich XML support.

Another approach, then, is to simply fill the DataSet object and use the ReadXml method to extract XML from the DataSet object. Both approaches solve the problem by loading all the data into memory and creating an XML representation. As you see repeatedly throughout this chapter, SQL Server's XML support provides you with a range of options for working with data as XML.

One way that SQL Server 2000 adds XML support is through the addition of the FOR XML clause. Using this new clause, you can query the database and have the results returned as XML rather than as a tabular resultset. Providing XML bypasses the need to load the tabular data into an interim object, such as ADO.NET, and loop through the records to create an XML representation. Using the FOR XML clause in a SQL statement causes the results to be returned as XML data rather than as tabular data.

The XML returned as a result of a FOR XML query does not contain a root node; it only represents the rows that are returned. When outputting the results from an XML query, add your own root element to make the XML well formed.

By default, elements map to a table or view, and attributes map to the table or view's columns. The FOR XML clause, however, provides several options for returning data that has other structures. The complete syntax of the FOR statement in transact SQL is represented with the following production:

 [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }
      [ , XMLDATA ]
      [ , ELEMENTS ]
      [ , BINARY BASE64 ]
    }
]

The parts of this production are described in Table 9.1.

Table 9.1 Arguments of the FOR XML Clause

Take a look at the FOR XML clause in detail.

FOR XML RAW

Using FOR XML RAW is the simplest form of returning data from SQL Server. Each row in the returned rowset is represented as an empty <row> element with attributes that represent the columns in the resultset. Listing 9.1 shows the query using FOR XML RAW.

Listing 9.1 A Sample FOR XML RAW Query

SELECT c.CustomerID, c.ContactName,
  o.ShipCity, e.FirstName + ' ' + e.LastName as EmployeeName, e.Region
FROM Customers c INNER JOIN
  Orders o ON c.CustomerID = o.CustomerID INNER JOIN
  Employees e ON o.EmployeeID = e.EmployeeID
WHERE (c.CustomerID IN ('ALFKI', 'TRAIH'))
FOR XML RAW

To execute this query, open up Query Analyzer for SQL Server, connect to the Northwind sample database, and paste the code from Listing 9.1 into the query window. Press the F5 key to run the query and see the results in the results pane.

The result of this query is shown in Listing 9.2. No white space is present in the actual output: It is formatted for readability.

Listing 9.2 Abbreviated Output of the Query Shown in Listing 9.1

<row CustomerID="ALFKI" ContactName="Maria Anders"
ShipCity="Berlin" _EmployeeName="Michael Suyama" />
<row CustomerID="ALFKI" ContactName="Maria Anders"
ShipCity="Berlin" EmployeeName="Margaret Peacock" Region="WA" />
.
.
.
<row CustomerID="TRAIH" ContactName="Helvetius Nagy"
ShipCity="Kirkland" _EmployeeName="Margaret Peacock" Region="WA" />
<row CustomerID="TRAIH" ContactName="Helvetius Nagy"
ShipCity="Kirkland" _EmployeeName="Anne Dodsworth" />
<row CustomerID="TRAIH" ContactName="Helvetius Nagy"
ShipCity="Kirkland" _EmployeeName="Michael Suyama" />

No root element is returned, so this is not well-formed XML. This issue is addressed later in this section when you use the XML classes in .NET to retrieve the data. Also notice the lack of the Region attribute in the highlighted rows. This is because the value is Null in the database: Null values simply are not returned in XML representation.

FOR XML AUTO

The AUTO argument of the FOR XML clause is a flexible means of generating XML data. It structures the XML hierarchy depending on the order of the columns in the SELECT list. This means that the order of the items in the SELECT list is imperative to the structure of the XML query. Listing 9.3 shows a sample query that uses FOR XML AUTO.

Listing 9.3 A Sample FOR XML AUTO Query

SELECT Customers.CustomerID, Customers.ContactName,
Orders.ShipCity, Employees.FirstName + ' ' + Employees.LastName as
_EmployeeName, Employees.Region
FROM Customers INNER JOIN
  Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
  Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE (Customers.CustomerID IN ('ALFKI', 'TRAIH'))
FOR XML AUTO

This query produces output, as shown in Listing 9.4. Again, no white space is present in the actual output for formatting and indention. This is provided for readability only.

Listing 9.4 Output of Listing 9.3

<Customers CustomerID="ALFKI" ContactName="Maria Anders">
   <Orders ShipCity="Berlin" EmployeeName="Michael Suyama">
     <Employees />
   </Orders>
   <Orders ShipCity="Berlin" EmployeeName="Margaret Peacock">
     <Employees Region="WA" />
     <Employees Region="WA" />
   </Orders>
   <Orders ShipCity="Berlin" EmployeeName="Nancy Davolio">
     <Employees Region="WA" />
     <Employees Region="WA" />
   </Orders>
   <Orders ShipCity="Berlin" EmployeeName="Janet Leverling">
     <Employees Region="WA" />
   </Orders>
</Customers>
<Customers CustomerID="TRAIH" ContactName="Helvetius Nagy">
   <Orders ShipCity="Kirkland" EmployeeName="Margaret Peacock">
     <Employees Region="WA" />
   </Orders>
   <Orders ShipCity="Kirkland" EmployeeName="Anne Dodsworth">
     <e />
   </Orders>
   <Orders ShipCity="Kirkland" EmployeeName="Michael Suyama">
     <Employees />
   </Orders>
</Customers>

The structure of the document was inferred from the order of columns in the SELECT list. Had you reordered the SELECT list, the output would look different. For example, change the SELECT list from what was shown in Listing 9.3 to what's shown in Listing 9.5.

Listing 9.5 Reordering the SELECT List in a Query Using FOR XML AUTO Produces Different Output Results

SELECT Orders.ShipCity, Employees.FirstName + ' ' + Employees.LastName as
 EmployeeName, Employees.Region, Customers.CustomerID, Customers.ContactName
.
.
.
FOR XML AUTO

Although the order of JOINs is the same, the document is reordered. List- ing 9.6 differs from Listing 9.4 because the hierarchical order of elements is reversed.

Listing 9.6 The Hierarchical Order of Document Nodes Is Affected by the Order of Columns Within the SELECT List Shown in Listing 9.5

<Orders ShipCity="Berlin" EmployeeName="Michael Suyama">
   <Employees>
     <Customers CustomerID="ALFKI" ContactName="Maria Anders" />
   </Employees>
</Orders>
<Orders ShipCity="Berlin" EmployeeName="Margaret Peacock">
   <Employees Region="WA">
     <Customers CustomerID="ALFKI" ContactName="Maria Anders" />
     <Customers CustomerID="ALFKI" ContactName="Maria Anders" />
   </Employees>
</Orders>
<Orders ShipCity="Berlin" EmployeeName="Nancy Davolio">
   <Employees Region="WA">
     <Customers CustomerID="ALFKI" ContactName="Maria Anders" />
     <Customers CustomerID="ALFKI" ContactName="Maria Anders" />
   </Employees>
</Orders>
<Orders ShipCity="Berlin" EmployeeName="Janet Leverling">
   <Employees Region="WA">
     <Customers CustomerID="ALFKI" ContactName="Maria Anders" />
   </Employees>
</Orders>
<Orders ShipCity="Kirkland" EmployeeName="Margaret Peacock">
   <Employees Region="WA">
     <Customers CustomerID="TRAIH" ContactName="Helvetius Nagy" />
   </Employees>
</Orders>
<Orders ShipCity="Kirkland" EmployeeName="Anne Dodsworth">
   <Employees>
     <Customers CustomerID="TRAIH" ContactName="Helvetius Nagy" />
   </Employees>
</Orders>
<Orders ShipCity="Kirkland" EmployeeName="Michael Suyama">
   <Employees>
     <Customers CustomerID="TRAIH" ContactName="Helvetius Nagy" />
   </Employees>
</Orders>

By retrieving XML in AUTO mode with the ELEMENTS argument, you can change the default behavior to return elements instead of attributes. You can change the query, as shown in Listing 9.7.

Listing 9.7 Specifying the ELEMENTS Argument in a Query Using FOR XML AUTO

SELECT Customers.CustomerID, Customers.ContactName,
  Orders.ShipCity, Employees.FirstName + ' ' + Employees.LastName as
_EmployeeName, Employees.Region
FROM Customers INNER JOIN
  Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
  Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE (Customers.CustomerID = 'TRAIH')
FOR XML AUTO, ELEMENTS

The result of this query is now changed from using attribute-centric form to element-centric form. Listing 9.8 shows that no attributes are used in the output; rather, the document is composed solely of elements.

Listing 9.8 Output of Listing 9.7

<Customers>
   <CustomerID>TRAIH</CustomerID>
   <ContactName>Helvetius Nagy</ContactName>
   <Orders>
     <ShipCity>Kirkland</ShipCity>
     <EmployeeName>Margaret Peacock</EmployeeName>
     <Employees>
        <Region>WA</Region>
     </Employees>
   </Orders>
   <Orders>
     <ShipCity>Kirkland</ShipCity>
     <EmployeeName>Anne Dodsworth</EmployeeName>
     <Employees />
   </Orders>
   <Orders>
     <ShipCity>Kirkland</ShipCity>
     <EmployeeName>Michael Suyama</EmployeeName>
     <Employees />
   </Orders>
</Customers>

The table names were used as the names of the XML elements up to this point. The names of the database tables used in the queries are in plural form. This usually indicates a collection of items rather than a grouping of a single item for XML developers. You can rename the output columns by aliasing the tables. Listing 9.9 shows a SQL query where the tables are aliased with uppercase names.

Listing 9.9 Aliasing the Table Names Within the SELECT List Affects the Element Names in the Resulting XML

SELECT CUSTOMER.CustomerID, CUSTOMER.ContactName,
  [ORDER].ShipCity, EMPLOYEE.FirstName + ' ' + EMPLOYEE.LastName as
_EmployeeName, EMPLOYEE.Region
FROM Customers CUSTOMER INNER JOIN
  Orders [ORDER] ON CUSTOMER.CustomerID = [ORDER].CustomerID INNER JOIN
  Employees EMPLOYEE ON [ORDER].EmployeeID = EMPLOYEE.EmployeeID
WHERE (CUSTOMER.CustomerID = 'TRAIH')
FOR XML AUTO, ELEMENTS

Listing 9.9 produces the results shown in Listing 9.10. Notice that the element names are now capitalized and in singular form.

Listing 9.10 Output of Listing 9.9

<CUSTOMER>
   <CustomerID>TRAIH</CustomerID>
   <ContactName>Helvetius Nagy</ContactName>
   <ORDER>
     <ShipCity>Kirkland</ShipCity>
     <EmployeeName>Margaret Peacock<
/EmployeeName>
     <EMPLOYEE>
        <Region>WA</Region>
     </EMPLOYEE>
   </ORDER>
   <ORDER>
     <ShipCity>Kirkland</ShipCity>
     <EmployeeName>Anne Dodsworth</EmployeeName>
     <EMPLOYEE />
   </ORDER>
   <ORDER>
     <ShipCity>Kirkland</ShipCity>
     <EmployeeName>Michael Suyama</EmployeeName>
     <EMPLOYEE />
   </ORDER>
</CUSTOMER>

An interesting inference that SQL Server makes when structuring the hierarchy is how it handles aliased or computed columns. It simply adds them as an attribute of the last table to which a column belonged. In Listing 9.10, the EmployeeName column was represented in the XML as a child of the ORDER element even though its data was retrieved from the Employees database table. You could move the column later in the SELECT list, but it turns out that there is another way of controlling the XML output and its structure.

FOR XML EXPLICIT

You saw that the AUTO mode query depends on the order of the columns in the select list. This yields a difficult to track bug in your application. Instead of relying on SQL Server's interpretation of the data's structure, you also have the capability of explicitly stating the document's structure by using EXPLICIT mode queries. To control the structure, you must use a specific syntax to control the hierarchy.

Tag and Parent Columns

The first step in creating an XML document explicitly with SQL Server is to use two special meta data columns in your queries:

• Tag—Stores the tag number of the current element.

• Parent—Specifies the Tag number of the element that will be this element's parent. If null, the element is placed at the top of the hierarchy.

The Tag and Parent combination defines how the data is hierarchically related.

Column Naming

The second step of using EXPLICIT mode is to specify the column names in the SELECT list. Column names have the following syntax:

ElementName!TagID!AttributeName!Directive

The parts of the column name are described in Table 9.2.

Table 9.2 Parts of Explicit Mode Column Name

The sample SQL statement in Listing 9.11 produces a single level in the hierarchy.

Listing 9.11 A Simple Query Using EXPLICIT Mode XML Querying

SELECT
   1        as Tag,
   NULL        as Parent,
   CustomerID     as [Customer!1!CustomerID],
   ContactName     as [Customer!1!ContactName]
FROM Customers
WHERE CustomerID IN ('ALFKI','TRAIH')
FOR XML EXPLICIT

This produces the XML output in Listing 9.12.

Listing 9.12 Output of Listing 9.11

<Customer CustomerID="ALFKI" ContactName="Maria Anders" />
<Customer CustomerID="TRAIH" ContactName="Helvetius Nagy" />

This approach can seem strange at first, but you really haven't done anything outstanding. If you execute the statement without the FOR XML EXPLICIT clause at the end, you'd have a resultset that looks similar to what's shown in Figure 9.1.

Figure 9.1 A sample resultset that shows the levels of a FOR XML EXPLICIT query.

The examples have been basic so far because you have only queried a single level in the hierarchy. Let's add another level.

Use the UNION operator with the ALL argument to create the levels in the hierarchy. Also use the Tag meta data column to specify the element's tag, and the Parent meta data column to specify which tag ID this element will be a child of. Listing 9.13 shows an explicit mode query that uses two levels of nesting to control the output XML hierarchy.

Listing 9.13 An Explicit Mode Query with Two Levels

SELECT
   1        as Tag,
   NULL        as Parent,
   CustomerID       as [Customer!1!CustomerID],
   ContactName       as [Customer!1!ContactName],
   NULL       as [Order!2!OrderID],
   NULL       as [Order!2!ShipCity]
FROM Customers
WHERE CustomerID IN ('ALFKI','TRAIH')

UNION ALL

SELECT
   2,
   1,
   c.CustomerID,
   NULL,
   o.OrderID,
   o.ShipCity

FROM Customers c INNER JOIN Orders o
   ON c.CustomerID = o.CustomerID
WHERE c.CustomerID IN ('ALFKI','TRAIH')

ORDER BY [Customer!1!CustomerID],[Order!2!OrderID]
FOR XML EXPLICIT

The first change you made was to add two new columns to the SELECT list. The new element, <Order>, references tag ID 2 and contains the attributes OrderID and ShipCity.

The second level in the hierarchy (which is highlighted) declares the tag ID of 2 and specifies that the element with tag ID of 1 is its parent in the hierarchy. This element level does not use column aliases: The first section in the UNION ALL operator specifies what the output columns are, the other sections in the UNION must provide the same number of columns for the UNION.

We specified the ContactName attribute to have a Null value in the second part of the UNION operation. Think about this for a second: The second level in the hierarchy does not include this in its data. You only need a column from its parent to specify which columns form the hierarchy.

Finally, using EXPLICIT mode requires an ORDER BY clause to determine how the columns should be related. The tabular relation is represented in Figure 9.2.

Figure 9.2 The tabular relation of columns in an EXPLICIT mode query.

In Figure 9.2, you can see the level of the hierarchy represented. The XML representation in Listing 9.14 is then easier to see.

Listing 9.14 Output of Listing 9.13

<Customer CustomerID="ALFKI" ContactName="Maria Anders">
   <Order OrderID="10643" ShipCity="Berlin" />
   <Order OrderID="10692" ShipCity="Berlin" />
   <Order OrderID="10702" ShipCity="Berlin" />
   <Order OrderID="10835" ShipCity="Berlin" />
   <Order OrderID="10952" ShipCity="Berlin" />
   <Order OrderID="11011" ShipCity="Berlin" />
</Customer>
<Customer CustomerID="TRAIH" ContactName="Helvetius Nagy">
   <Order OrderID="10574" ShipCity="Kirkland" />
   <Order OrderID="10577" ShipCity="Kirkland" />
   <Order OrderID="10822" ShipCity="Kirkland" />
</Customer>

Specifying Directives

After you have created the basic hierarchical structure of the document, you can further refine the structure by using directives, as shown in Table 9.3.

Table 9.3 Directives for Column Naming

select 1 as tag,
   null as parent,
   CompanyName   as [Customer!1!CompanyName!element]
from Customers where CustomerID = 'TRAIH'
for XML explicit

<Customer>
  <CompanyName>Trail&apos;s Head
  Gourmet Provisioners</CompanyName>
</Customer>

select 1 as tag,
   null as parent,
   '<MYTAG />'     as [CustomTags!1!!xml]
for XML explicit

<CustomTags><MYTAG /></CustomTags>

   CompanyName     as [Customer!1!!cdata]
from Customers where CustomerID = 'TRAIH'
for XML explicit

Putting It Together

Listing 9.15 shows a full example of a stored procedure that returns XML from an EXPLICIT mode XML query.

Listing 9.15 Example of an EXPLICIT Mode XML Query in SQL Server

create procedure GetCustomerXML(@CustomerID char(5))
as

select
   1     as Tag,
   NULL     as Parent,
   CustomerID     as [Customer!1!CustomerID],
   CompanyName     as [Customer!1!CompanyName],
   NULL        as [Order!2!OrderID!id],
   NULL        as [Order!2!ShipVia],
   NULL        as [Order!2!ShippedDate],
   NULL        as [Order!2!ShipName],
   NULL       as [OrderDetails!3!OrderID!idref],
   NULL       as [OrderDetails!3!ProductID!idref],
   NULL       as [OrderDetails!3!UnitPrice!element],
   NULL       as [OrderDetails!3!Quantity!element],
   NULL       as [OrderDetails!3!Discount!element],
   NULL          as [Product!4!ProductID!id],
   NULL          as [Product!4!ProductName],
   NULL          as [Product!4!Category],
   NULL          as [Product!4!QuantityPerUnit]
from Customers where CustomerID = @CustomerID

UNION ALL

select
   2,
   1,
   CustomerID,
   NULL,
   o.OrderID,
   s.CompanyName,
   o.ShippedDate,
   o.ShipName,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL
FROM Orders o INNER JOIN Shippers s ON o.ShipVia = s.ShipperID
WHERE o.CustomerID = @CustomerID

UNION ALL

select
   3     as Tag,
   2     as Parent,
   o.CustomerID,
   NULL,
   d.OrderID,
   NULL,
   NULL,
   NULL,
   d.OrderID,
   d.ProductID,
   d.UnitPrice,
   d.Quantity,
   str(d.Discount,4,2),
   NULL,
   NULL,
   NULL,
   NULL
FROM     [Order Details] d INNER JOIN
           Orders o ON d.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID

UNION ALL

select
   4     as Tag,
   3     as Parent,
   o.CustomerID,
   NULL,
   o.OrderID,
   NULL,
   NULL,
   NULL,
   o.OrderID,
   p.ProductID,
   NULL,
   NULL,
   NULL,
   p.ProductID,
   p.ProductName,
   c.CategoryName,
   p.QuantityPerUnit
FROM     dbo.[Order Details] d INNER JOIN
           dbo.Orders o ON d.OrderID = o.OrderID INNER JOIN
           dbo.Products p ON d.ProductID = p.ProductID INNER JOIN
           dbo.Categories c ON p.CategoryID = c.CategoryID
   WHERE o.CustomerID = @CustomerID

ORDER BY [Customer!1!CustomerID],[Order!2!OrderID!id],[OrderDetails!3!OrderID!idref],
[_OrderDetails!3!ProductID!idref],[Product!4!ProductID!id]
FOR XML EXPLICIT,XMLDATA

Listing 9.15 is available on the book's website as GetCustomerXML.sql.

Note several points about Listing 9.15. Directives specify id type attributes for Orders.OrderID and Products.ProductID. We also used idref directives to refer to these attributes (see OrderDetails.OrderID and OrderDetails.ProductID). The id type attribute speficies the value is unique, while the idref type refers to a specified unique id. You also specificed that the UnitPrice, Quantity, and Discount columns should be output as elements rather than as attributes. Finally, you used the XMLDATA argument to the FOR XML clause to specify that an inline schema should be generated.

To retrieve the XML results, you can load the data into a DataSet or you can use the ExecuteXmlReader method of the SqlCommand object, as shown in Listing 9.16.

Listing 9.16 Using the SqlCommand.ExecuteXmlReader to Retrieve XML Data from SQL Server

<%@ Import Namespace="System.Configuration"%>
<%@ Import Namespace="System.Xml"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Import Namespace="System.Data"%>

<script language="C#" runat="server">
     private void Page_Load(object sender, System.EventArgs e)
     {
        Response.ContentType="text/xml";

        string customerID = Request.QueryString.Get("CustomerID");

        if (customerID == null )
        {
          Response.Write ("<ERROR><![CDATA[The CustomerID parameter _was 
          not specified.]]></ERROR>");
        }
        else
        {
          string connectionString = ConfigurationSettings.AppSettings
          ["connectionString"];
          XmlReader reader = null;
          XmlTextWriter writer = null;
          SqlConnection connection = new_SqlConnection(connectionString);
          SqlCommand command = new_SqlCommand("GetCustomerXML",connection);
          command.CommandType = CommandType.StoredProcedure ;
          SqlParameter param =__command.Parameters.Add("@CustomerID",
          SqlDbType.Char,5);
          param.Direction = ParameterDirection.Input;
          param.Value = customerID;
          try
          {
             connection.Open();
             reader = command.ExecuteXmlReader();
             writer = new XmlTextWriter             
             _(Response.OutputStream,System.Text.Encoding.UTF8);
             writer.WriteStartDocument(true);
             writer.WriteStartElement("DATA");

             while(reader.Read())
             {
               switch(reader.NodeType)
               {
                  case XmlNodeType.Element:
                     writer.WriteStartElement(reader.Prefix,
                     reader.LocalName,reader.NamespaceURI);
                     if (reader.HasAttributes)
                     {
                    writer.WriteAttributes(reader,true);
                    reader.MoveToElement();
                     }
                     if (reader.IsEmptyElement)
                  writer.WriteEndElement();
                      break;
                         case XmlNodeType.EndElement:
                         writer.WriteEndElement();
                      break;
                         case XmlNodeType.Text:
                         writer.WriteString      (reader.Value);
                      break;
                         case XmlNodeType.CDATA:
                         writer.WriteCData       (reader.Value);
                      break;
               }
             }
             writer.WriteEndDocument();
          }
          catch(Exception oops)
          {
             Response.Write ("<ERROR><![CDATA[" + Server.HtmlEncode
_(oops.ToString()) + "]]></ERROR>");
          }
          finally
          {

             if (writer != null)
               writer.Close();
                  if (reader != null)
               reader.Close();
                  connection.Close();
                  command.Dispose();
                  connection.Dispose();
          }
        }
     }
</script>

This page was executed by creating a new web form called CustomerOrders.aspx in the current project, pasting the preceding C# code into the CustomerOrders.aspx file, building the project, and typing the following URL into the browser: http://localhost/chapters/10/SQLServerXMLCS/CustomerOrders.aspx?CustomerID=ALFKI.

The results of this example are shown in Listing 9.17.

Listing 9.17 Output Results of an EXPLICIT Mode Query that Specified the XMLDATA Option

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<DATA>
 <Schema name="Schema1"
   xmlns="urn:schemas-microsoft-com:xml-data"
   xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="Customer" content="mixed" model="open">
   <AttributeType name="CustomerID" dt:type="string" />
   <AttributeType name="CompanyName" dt:type="string" />
   <attribute type="CustomerID" />
   <attribute type="CompanyName" />
  </ElementType>
  <ElementType name="Order" content="mixed" model="open">
   <AttributeType name="OrderID" dt:type="id" />
   <AttributeType name="ShipVia" dt:type="string" />
   <AttributeType name="ShippedDate" dt:type="dateTime" />
   <AttributeType name="ShipName" dt:type="string" />
   <attribute type="OrderID" />
   <attribute type="ShipVia" />
   <attribute type="ShippedDate" />
   <attribute type="ShipName" />
  </ElementType>
  <ElementType name="OrderDetails" content="mixed" model="open">
   <AttributeType name="OrderID" dt:type="idref" />
   <AttributeType name="ProductID" dt:type="idref" />
   <attribute type="OrderID" />
   <attribute type="ProductID" />
   <element type="UnitPrice" />
   <element type="Quantity" />
   <element type="Discount" />
  </ElementType>
  <ElementType name="UnitPrice" content="textOnly" 
      model="closed" dt:type="fixed.14.4" />
  <ElementType name="Quantity" content="textOnly" 
      model="closed" dt:type="i2" />
  <ElementType name="Discount" content="textOnly" 
      model="closed" dt:type="string" />
  <ElementType name="Product" content="mixed" model="open">
   <AttributeType name="ProductID" dt:type="id" />
   <AttributeType name="ProductName" dt:type="string" />
   <AttributeType name="Category" dt:type="string" />
   <AttributeType name="QuantityPerUnit" dt:type="string" />
   <attribute type="ProductID" />
   <attribute type="ProductName" />
   <attribute type="Category" />
   <attribute type="QuantityPerUnit" />
  </ElementType>
 </Schema>
 <Customer xmlns="x-schema:#Schema1" CustomerID="ALFKI" 
     CompanyName="Alfreds Futterkiste">
  <Order OrderID="10643" ShipVia="Speedy Express"
ShippedDate="1997-09-02T00:00:00" ShipName="Alfreds Futterkiste">
   <OrderDetails OrderID="10643" ProductID="28">
    <UnitPrice>45.6000</UnitPrice>
    <Quantity>15</Quantity>
    <Discount>0.25</Discount>
    <Product ProductID="28" ProductName="Rössle Sauerkraut" 
      Category="Produce" QuantityPerUnit="25 - 825 g cans" />
   </OrderDetails>
   <OrderDetails OrderID="10643" ProductID="39">
    <UnitPrice>18.0000</UnitPrice>
    <Quantity>21</Quantity>
    <Discount>0.25</Discount>
    <Product ProductID="39" ProductName="Chartreuse verte" 
       Category="Beverages" QuantityPerUnit="750 cc per bottle" />
   </OrderDetails>
   <OrderDetails OrderID="10643" ProductID="46">
    <UnitPrice>12.0000</UnitPrice>
    <Quantity>2</Quantity>
    <Discount>0.25</Discount>
    <Product ProductID="46" ProductName="Spegesild" Category="Seafood"
QuantityPerUnit="4 - 450 g glasses" />
   </OrderDetails>
  </Order>
.
.
.
 </Customer>
</DATA>

Notice that the generated schema reflects the structure of the individual SELECT statements instead of the overall structure of all UNION operations. This inline schema can be useful for quickly creating DataSets in ADO.NET. This flexibility comes at a price: Creating schemas on the server introduces overhead and impacts performance. Consider creating your own schema against the expected results of a FOR XML query and caching it for validation or specifying schemas for DataSets, as you saw in Chapter 7, "ASP.NET Extensibility with XML."

The ability to generate XML directly from SQL Server yields some interesting design patterns and system architectures. For example, you can generate web reports by using XSLT stylesheets directly against the XML data that's returned from SQL Server, which enables dynamic filtering, sorting, and a range of interactive DHTML capabilities.

OPENXML

Besides using the FOR XML clause to return XML data, Microsoft SQL Server 2000 also provides the OPENXML function to shred an XML document and provide a rowset representation of the XML data.

As with many Win32 API calls, a function must be called to obtain a handle to an internal resource. You then work with the handle reference instead of the actual resource, then release the resource with a separate API call. Figure 9.3 depicts the steps required to shred an XML document.

Figure 9.3 Shredding an XML document by using OPENXML.

To shred an XML document using OPENXML, a handle to a DOM representation of the document is obtained using sp_xml_preparedocument. After the rowset has been obtained using the OPENXML function, sp_xml_removedocument needs to be called to remove the document from memory.

The OPENXML function accepts three parameters:

• A handle to an open document in memory

• An XPath pattern dictating the rowset to return

• A flag that specifies the data structure

The following sections address obtaining the resource handle and supported XPath queries. But first, look at the flags that are available for OPENXML.

The flags argument of OPENXML specifies if the document is modeled as attribute-centric or element-centric, or both. Table 9.4 describes these flags in detail.

Table 9.4 Flags Argument of OPENXML

To obtain the handle to the in-memory resource, two system stored procedures are used: sp_xml_preparedocument and sp_xml_removedocument.

sp_xml_preparedocument

In order to represent an XML document as a rowset, the input XML is first parsed and loaded into an internal DOM object. The DOM is retained in memory, and a handle to the open DOM is returned as an OUTPUT parameter.

Because the entire document is retained in memory, it is advisable to limit the size of documents used with OPENXML.

sp_xml_removedocument

Because the DOM document is retained in memory, its memory must be explicitly deallocated. Calling sp_xml_removedocument is a crucial step in successfully deploying applications that use OPENXML. Failure to do so leaves the document in memory. For example, the following code creates an in-memory DOM representation of the XML and outputs the handle to the created resource. Simply enter this code into the query window in Query Analyzer and run the query:

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='<data>
   <customers>
     <CustomerID>ALFKI</CustomerID>
     <CompanyName>Alfreds Futterkiste</CompanyName>
   </customers>
</data>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
select @idoc

Run this code several times. Note the number that is output each time.

Now execute the following statement in Query Analyzer using a number that was output by executing the preceding sp_xml_preparedocument procedure:

exec sp_xml_removedocument 79

Executing sp_xml_removedocument deallocates the memory used to represent the DOM in memory. You can see how failure to deallocate copies of each DOM representation can add up to much wasted memory. Unless you retain a running list of handles to open DOM documents, they cannot be retrieved for later removal.

WITH Clause

After the handle to the open DOM resource is obtained from sp_xml_preparedocument, it is shredded by using the OPENXML function, as shown in Listing 9.18. The WITH clause specifies the elements in the document that are used to satisfy the SELECT list.

Listing 9.18 Using the sp_xml_preparedocument and sp_xml_removedocument System Procedures

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='<data>
   <customers>
     <CustomerID>ALFKI</CustomerID>
     <CompanyName>Alfreds Futterkiste</CompanyName>
     <ContactName>Maria Anders</ContactName>
     <ContactTitle>Sales Representative</ContactTitle>
     <Address>Obere Str. 57</Address>
     <City>Berlin</City>
     <PostalCode>12209</PostalCode>
     <Country>Germany</Country>
     <Phone>030-0074321</Phone>
     <Fax>030-0076545</Fax>
   </customers>
   <customers>
     <CustomerID>AROUT</CustomerID>
     <CompanyName>Around the Horn</CompanyName>
     <ContactName>Thomas Hardy</ContactName>
     <ContactTitle>Sales Representative</ContactTitle>
     <Address>120 Hanover Sq.</Address>
     <City>London</City>
     <PostalCode>WA1 1DP</PostalCode>
     <Country>UK</Country>
     <Phone>(171) 555-7788</Phone>
     <Fax>(171) 555-6750</Fax>
   </customers>
</data>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT  *
FROM    OPENXML (@idoc, 'data/customers[CustomerID=''AROUT'']',2)
      WITH (CustomerID nchar(5),
      CompanyName nvarchar(20))
exec sp_xml_removedocument @idoc

Executing this query causes a rowset to be returned:

CustomerID CompanyName
---------- --------------------
AROUT      Around the Horn

Querying an XML document to return a rowset cannot seem that functional. You can, however, use OPENXML to perform INSERT, UPDATE, and DELETE statements. This enables you to specify an XML document to be used in mass insertions or updates to multiple tables. Listing 9.19 shows you how a table is inserted into an XML document.

Listing 9.19 Using an XML Document as the Basis for Inserting Records into a Table

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='<data>
   <customers>
     <CustomerID>VBDNA</CustomerID>
     <CompanyName>Kirk Allen Evans</CompanyName>
   </customers>
</data>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

INSERT INTO Customers (CustomerID, CompanyName)
SELECT  *
FROM    OPENXML (@idoc, 'data/customers[CustomerID=''VBDNA'']',2)
      WITH (CustomerID nchar(5),
     CompanyName nvarchar(20))

exec sp_xml_removedocument @idoc

Because the OPENXML function returns a rowset, you can easily combine it with other clauses, such as EXISTS and IN, to dynamically modify data in the database. For example, you can create a stored procedure that accepts an XML document as a parameter, where the XML document contains the data to be modified. This capability, combined with the ability to access SQL Server over HTTP, provides some clever possibilities.

If the input XML document contains more data than there are columns defined, OPENXML enables you to store the overflow data in a column so that it can be retrieved later.

Querying SQL Server over HTTP

SQL Server 2000 introduced the ability to query the database over HTTP by leveraging virtual directories in IIS. Because the results are delivered as XML over the web, data can be consumed over disparate platforms and from remote locations. This reduces the complexity of your applications by reducing the amount of coding necessary to produce the XML data and provide error handling.

Configuring SQL XML Support in IIS

To support XML over HTTP, SQL Server provides a Microsoft Management Console (MMC) snap-in that configures SQL XML support in IIS.

To use this snap-in, select Configure SQL XML Support in IIS from the Start menu. When you open the snap-in, expand the nodes to display the default website. Right-click the Default website and from the Context menu, click New, Virtual Directory. This brings up a property sheet with several tabs.

The General Tab

On the General tab, set the virtual directory name as Northwind, and set the local path to a file path that you have access to. For example, I used a local folder on my D: drive:

D:\projects\New Riders\chapters\SQL Server

The Security Tab

The Security Tab enables you to specify the security used when working with SQL Server over HTTP. For example, you can be accessing the website using the IUSR_MachineName account because you are allowing anonymous access to the website. You don't want to add the IUSR_MachineName account as a user of the SQL Server database. Instead, you can specify what security model is used when accessing the database. Table 9.5 shows you explanations of this tab's options.

Table 9.5 Security Tab Options

The Data Source Tab

The Data Source tab specifies the server that you are querying and the database on that server that is to be queried. These settings are the same server and database used when accessing a database by using an ADO.NET connection string. I used my local server and specified the Northwind database, which is used for the examples throughout this chapter.

The Settings Tab

The Settings tab enables you to specify what actions can be performed for this virtual directory. Valid application concerns exist for why you would not want the users to perform certain actions. For example, the Allow POST option specifies that the user can use an HTTP POST to post his own template files (explored later in the section, "Executing Template Files") and enables restrictions on what size those template files can be.

You can also want to restrict the capability of the user to run ad-hoc queries against the database using the URL Queries option. Figure 9.4 shows that we are going to check all the options for demonstration purposes.

Figure 9.4 Check all the options on the Settings tab for demonstration purposes.

The Virtual Names Tab

The Virtual Names tab specifies a virtual name that's part of the URL. Three different types of virtual names are used: a database object, a schema file, or a template file. The virtual name enables you to associate a subdirectory name with a specific type of access. The use of virtual names is discussed in detail later in this chapter. (Using SQL Server to access database objects over HTTP is discussed in the section, "Accessing Database Objects," and the use of template files is discussed in the section, "Executing Template Files.")

SQL Server Books Online (BOL) explains the different tabs and settings in the snap-in. However, only use the IIS Virtual Directory Management for SQL Server utility to modify virtual directories that expose SQL Server: Special permissions for accessing SQL Server are set by this MMC snap-in that the more familiar MMC snap-in for IIS does not provide.

The remainder of this chapter uses the Northwind database. The virtual directory is also named Northwind, and permission is granted for URL access, template access, and XPath queries. Figure 9.5 depicts the finished version of the virtual directory in the MMC snap-in.

Figure 9.5 The properties of the sample Northwind virtual directory in the SQL Server MMC snap-in for virtual directory management.

Querying over HTTP

A common task in developing web applications is providing data that can be consumed from remote sources. Different approaches to solving this problem can range from using FTP to transfer files to embedding data in an HTML page that is to be screen-scraped. As you will see in Chapter 11, "Creating and Using ASP.NET Web Services," you can use XML to access methods remotely using SOAP.

Another method for providing data to remote clients is to provide XML data over the web through an HTTP request. This section discusses how SQL Server provides a rich mechanism for providing XML data over the web, while allowing secure access to server resources.

After a virtual directory is created with the properties shown in Figure 9.5, you can begin to query SQL Server over HTTP. There are several ways to do this:

• Include URL-encoded SQL in the URL

• Access database objects directly

• Execute templates

Encoding SQL in the URL

When sending SQL Data Modification Language (DML) commands in a URL, such as INSERT, UPDATE, or DELETE statements, the SQL string is represented in a QueryString item named sql. Remember that query results returned from FOR XML queries might not be well-formed XML. You can optionally provide a root element to contain the results of the query by specifying a QueryString item named root that indicates the name of the root element.

We named the virtual directory Northwind and made it a child of the root web so that we can open a browser and type the following:

http://localhost/northwind?sql=select%20*%20from
   %20customers%20for%20xml%20au_to, 
   elements&root=DATA

This query produces the following XML in the browser:

<?xml version="1.0" encoding="utf-8" ?>
<DATA>
   <customers>
     <CustomerID>ALFKI</CustomerID>
     <CompanyName>Alfreds Futterkiste</CompanyName>
   </customers>
</DATA>

Think about what happened here. You entered a SQL query into a URL. The URL was parsed by an Internet Information Services API (ISAPI) filter that recognized the reserved QueryString elements sql and root. The ISAPI filter then forwarded the request to SQL Server by using the specified authentication (we leveraged "Windows Integrated Authentication" in the example), which returned the results of the query back to the browser over HTTP. This tremendous amount of flexibility is provided with a few simple settings.

Take a closer look at what was actually sent to SQL Server. We talked about the sql and root QueryString variables at the beginning of this section. Notice the %20 between each word in the sql QueryString variable. Because the SQL is part of the URL, the complete URL string must still conform to URL encoding specifications. As you will see throughout the remainder of this chapter, we use the convenient Server.URLEncode method to return an encoded URL string.

Another interesting point is that no file is specified to retrieve in the URL; we only specified a root directory to navigate to. There is no file with a .html extension or a .aspx extension: The ISAPI filter responds to requests to access the virtual directory.

You can see that any query that is valid for SQL Server is also valid to use within the URL. For example, the following code uses a WHERE clause and names individual column names in the SELECT list:

http://localhost/northwind?sql=select%20CustomerID,
CompanyName%20from%20customers%20where%20customerid='
ALFKI'%20for%20xml%20auto,elements&root=Customers

Calling Stored Procedures

Because any query is valid, you can also access stored procedures through a URL. Let's create a stored procedure called GetCustomersXML. Open SQL Query Analyzer from the SQL Server program group in your Start menu. Connect to the Northwind database and enter the text from Listing 9.20 into the text window.

Listing 9.20 Create a Stored Procedure to Return XML

CREATE procedure GetCustomersXML(@CustID varchar(5))
as
declare @SearchVar varchar(6)

select @SearchVar = @CustID + '%'
SELECT * FROM Customers WHERE CustomerID LIKE 
@SearchVar for XML AUTO,ELEMENTS

This stored procedure enables you to enter up to five characters to search for a customer by CustomerID and display the results as XML. Press the F5 key to create the stored procedure. You can test the stored procedure by entering the following text into the query pane, highlighting the text, and pressing F5:

GetCustomersXML 'ALFKI'

You should see an XML string in the results window that contains the data for CustomerID "ALFKI". Now that you have a stored procedure to test with, you can execute the procedure through a URL in the browser:

http://localhost/northwind?sql=GetCustomersXML+'ALFKI'&root=DATA

Another interesting capability is the ability to call system stored procedures or Database Console Commands (dbcc commands) through the URL, provided that you have obtained authentication as specified in the MMC snap-in for administering XML support for SQL Server. As long as you have the privileges to invoke it, it is perfectly legal to issue dbcc commands using the URL. The following example executes the dbcc command freeproccache, which clears the system cache in the database:

http://localhost/northwind?sql=dbcc%20freeproccache

This example does not provide a return or recordset, so it is easy to call. However, suppose that you want to call a system stored procedure or a dbcc command that returns a recordset. This issue is addressed in the following sections.

Accessing Database Objects

Because SQL Server adds XML support to its database, you also have the option of querying database objects, such as tables or views, directly through the URL by using XPath syntax. This adds flexibility that users unfamiliar with T-SQL or SQL syntax can use to access and navigate your database.

To access a database object directly through the URL, a virtual name must be added to your virtual directory that enables access to database objects.

Follow these steps to create a virtual name through which you can access a database object over HTTP:

1. Go into the IIS Virtual Directory Management for SQL Server MMC snap-in and choose the Northwind virtual directory that was created at the beginning of this section.

2. Right-click and choose Properties to show the property sheet for the virtual directory.

3. Click the Virtual Names tab, click the New button, and enter a name for the new virtual name. For example, I chose myvirt as a name.

4. Select dbobject from the type drop-down list and click Save. This enables access to database objects through the virtual name myvirt.

By using this virtual name, you can specify the database object to be queried using XPath syntax. The following code selects the CompanyName column from the Customers table where the CustomerID column's value is 'ALFKI':

http://localhost/northwind/myvirt/Customers[@CustomerID='ALFKI']/@CompanyName

You should receive the following code in your browser:

Alfreds Futterkiste

This code retrieved only one column. What about retrieving multiple columns? Recall that SQL Server uses a single-column resultset when providing XML using FOR XML queries. Because there is only one column, no column delimiters are needed in the result. This makes streaming individual rows simpler. When accessing database objects directly through the URL, you have to structure your XPath query to only retrieve a single-column result, or else SQL Server cannot provide a stream representation. To better understand this process, try retrieving multiple columns using an XPath query. The following XPath statement attempts to retrieve all columns for the Customers table:

http://localhost/northwind/myvirt/Customers[@CustomerID='ALFKI']/@*?root=data

Instead of retrieving all columns, you receive an error similar to the following:

<?xml version="1.0" encoding="utf-8" ?>
<data>
   <?MSSQLError HResult="0x80004005" Source="Microsoft XML 
     Extensions to   _SQL Server"
Description="Streaming not supported over multiple column   _result"?>
</data>

Accessing database objects directly through the URL provides limited functionality. There is also not a mechanism to select which database objects are exposed through the URL and which are not without relying on the security scheme (SQL Server or Windows authentication, which is set up on the Security tab in the SQL Server XML MMC snap-in). SQL Server provides a more attractive mechanism for exposing your database through templates.

Executing Template Files

Allowing users to execute queries through the URL is useful, but can also have adverse effects on your database. Nothing prevents the user from entering the following query:

SELECT c.*, o.*, p.*
from customers c, orders o, products p

The result of this query is an extremely large resultset that puts a large burden on SQL Server to generate the results, IIS to deliver the results, and the client browser to parse and display the results. If many users ran this query within a short period of time, it would bring the database and, likely, the web servers to a screeching halt.

Instead of providing direct query access through the URL, you can create predefined queries and store them in template files on the server. A template file is an XML file that includes certain elements outlined in this section. This XML file can contain one or more SQL statements and XPath queries. The benefit to using template files is that you can provide predefined queries that return XML over the web, which enables you to have more control over what users can access over the web.

One way to think of a template file is like a database view—it's a predefined way to query the database. A better analogy might be a stored procedure. A stored procedure can contain multiple queries that are to be executed. Template files can also contain multiple SQL statements, but they add the capability to work with the SQL Server database like a set of XML documents using XPath syntax.

Creating Templates

Before you create a template file, make sure that the option to execute templates is checked in the SQL Server Virtual Directory Management MMC snap-in, as shown in Figure 9.4. Unless this option is checked, you'll receive HTTP error 400, Bad Request.

Listing 9.21 shows what a template file looks like.

Listing 9.21 Syntax for Creating Template Files for Use with SQL Server Virtual Directories

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
    sql:xsl='XSL FileName' >
 <sql:header>
  <sql:param>..</sql:param>
  <sql:param>..</sql:param>...n
 </sql:header>
 <sql:query>
  sql statement(s)
 </sql:query>
 <sql:xpath-query mapping-schema="SchemaFileName.xml">
  XPath query
 </sql:xpath-query>
</ROOT>

Break up the template example into its individual parts. Table 9.6 shows an explanation of the elements and attributes that might appear in an XML template file.

Table 9.6 Elements and Attributes of a Template File

Now that you have seen how to construct a template file, let's walk through an example. First, you need to set up your virtual directory to support templates and specify where the files are physically located.

When you set up the virtual name to access database objects in the preceding section, you were given a choice of a type of virtual name when configuring a new virtual name. This is how the MMC snap-in enables you to specify where the template files physically reside.

Follow these steps to create a virtual name to access your template files over HTTP:

1. Open the MMC snap-in for SQL Server virtual directory management.

2. Open the properties for the Northwind virtual directory.

3. Click the Virtual Names tab to see the list of virtual names that are already defined.

4. Click the New button and a dialog box titled Virtual Name Configuration.

5. Type the virtual name as mytemplates, the Type is template, and the path as a valid path on your server's file system that holds the template files. For example, I used a relative path called templates that would be a subdirectory of the directory specified when creating the SQL Server virtual directory.

6. On the General tab for the virtual directory's property sheet, specify the local path for the virtual directory. When I originally created the virtual directory, I used a local path on my D: drive:

D:\projects\New Riders\Chapters\SQL Server

After specifying a relative path for your template files, the physical location of the template files translates to the following:

D:\projects\New Riders\Chapters\SQL Server\templates

By using a relative path, you are basically going one level deeper in the physical path.

The URL to your virtual directory is http://localhost/Northwind. Append the virtual name to the end of the URL for your virtual directory. For example, I used the new virtual name, mytemplates, so I can append this virtual name to the SQL Server virtual directory to access the virtually named object:

http://localhost/Northwind/mytemplates

At this point, you have only named the location where your template files will be stored, but we have not yet created a template.

Create a simple template file that consists of two queries: one that selects a row from the Customers table, and one that selects a row from the Products table. Also specify the root element to be named Data instead of ROOT to demonstrate that the name of the root node does not matter; it only needs to be a valid XML name. Listing 9.22 shows the finished result.

Listing 9.22 A Sample Query Template File

<Data xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <sql:query>
  SELECT CustomerID, CompanyName
  FROM Customers
  WHERE CustomerID = 'AROUT'
   FOR XML AUTO, ELEMENTS
 </sql:query>
 <sql:query>
   SELECT ProductID, ProductName
   FROM Products
   WHERE ProductID = 1
   FOR XML AUTO, ELEMENTS
 </sql:query>
</Data>

Because the template specified that the root element would be named Data, all data returned from within the template's body will be contained within the root Data element. The data from the Customers table will be a sibling with the data from the Products table.

Save this template file as sampleselect.xml into the templates folder specified earlier. The full path to the sample file on my directory is the following:

D:\projects\New Riders\Chapters\SQL Server\templates\sampleselect.xml

To execute this template, use the URL to the template file by using the virtual name that you set up at the beginning of this section:

http://localhost/Northwind/mytemplates/sampleselect.xml

By entering this URL into the browser window, the XML document in Listing 9.23 is displayed in the browser.

Listing 9.23 Output for Listing 9.22

<Data xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <Customers>
     <CustomerID>AROUT</CustomerID>
     <CompanyName>Around the Horn</CompanyName>
   </Customers>
   <Products>
     <ProductID>1</ProductID>
     <ProductName>Chai</ProductName>
   </Products>
</Data>

In Table 9.6, you saw that you can specify parameters to templates by using the <sql:query> element. You will create a new template in Listing 9.24 that will call the stored procedure you created in Listing 9.20.

Listing 9.24 Using a Parameter Within a Template File

<?xml version="1.0" encoding="utf-8" ?>
<Data xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <sql:header>
   <sql:param name="CustomerID">ALFKI</sql:param>
 </sql:header>
 <sql:query>
  exec GetCustomersXML @CustomerID
 </sql:query>
</Data>

Save this file as sampleparameter.xml in the templates directory. After the XML file is saved, you can then execute the template file by entering the URL to the file in the browser, as shown here:

http://localhost/northwind/mytemplates/sampleparameter.xml

The results are shown in Listing 9.25.

Listing 9.25 Output of Listing 9.24

<?xml version="1.0" encoding="utf-8" ?>
<Data xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <Customers>
     <CustomerID>ALFKI</CustomerID>
     <CompanyName>Alfreds Futterkiste</CompanyName>
     <ContactName>Maria Anders</ContactName>
     <ContactTitle>Sales Representative</ContactTitle>
     <Address>Obere Str. 57</Address>
     <City>Berlin</City>
     <PostalCode>12209</PostalCode>
     <Country>Germany</Country>
     <Phone>030-0074321</Phone>
     <Fax>030-0076545</Fax>
   </Customers>
</Data>

As you can see, specifying SQL queries using XML templates is significantly easier and less error prone than entering the URL-encoded equivalent in the Address bar of a browser. This also enables you, as the site administrator, to control access to the database through such an open mechanism as the web.

Posting Templates

Suppose that you want to execute a set of queries and the amount of text in the URL makes the query cumbersome, error prone, and difficult to debug. Instead of asking the site's administrator to add a custom template file for you, you can have him grant you permission to post template files to the virtual directory to be processed. The ability to use HTTP POST to send templates to SQL Server to be processed enables the user to dynamically construct queries and execute them on the database without having to resort to using the URL or having static template files on the server. By using this feature, you can give ad-hoc access to your users to query any combination of data. Just as with the URL, be forewarned that your users have the capability to execute extremely long-running queries.

To post a template, simply use an HTTP POST to send the template as a stream to the virtual directory.

For simplicity, generate an HTTP POST using HTML. In Listing 9.26, you use client-side HTML to post the contents of the TEXTAREA to the Northwind virtual directory directly. Inside the TEXTAREA, you specify the template that's to be posted to the virtual directory. You also specify the name of the text box as CustomerID to match the parameter specified in the query template.

Listing 9.26 Example of Using HTTP POST to Execute Templates

<HTML>
   <HEAD>
     <title>TemplatePost</title>
   </HEAD>
   <body MS_POSITIONING="GridLayout">
     <form id="TemplatePost" method="post" action=      
      _"http://localhost/northwind">
        &nbsp; <input type="text" name="CustomerID" 
       value="ALFKI" style="Z-INDEX: 101; LEFT: 187px;
POSITION: absolute;_TOP: 52px">
        <input type="hidden" name="contenttype" 
         value="text/xml"_style="Z-INDEX: 102; LEFT: 188px;
POSITION: absolute; TOP: 17px">&nbsp;&nbsp;
        <TEXTAREA name="template" rows="12" cols="51" 
         style="Z-INDEX: 103; LEFT: 44px; WIDTH: 428px;
POSITION: absolute;_TOP: 91px; HEIGHT: 190px">
&lt;Data xmlns:sql="urn:schemas-microsoft-com:xml-sql"&gt;
&lt;sql:header&gt;
   &lt;sql:param name="CustomerID"&gt;ALFKI&lt;/sql:param&gt;
&lt;/sql:header&gt;
&lt;sql:query&gt;
   exec GetCustomersXML @CustomerID
&lt;/sql:query&gt;
&lt;/Data&gt;
        </TEXTAREA> <input type="submit" 
        name="submitform"_value="Submit Query" style="
Z-INDEX: 104; LEFT: 339px; POSITION: absolute; TOP: 302px">
       <DIV style="DISPLAY: inline; Z-INDEX: 105; 
        LEFT: 45px;_WIDTH: 130px; POSITION: absolute;
TOP: 54px; HEIGHT: 21px"_ms_positioning="FlowLayout">Customer
          ID:</DIV>
     </form>
   </body>
</HTML>

Using Stylesheets With Templates

You can also associate a stylesheet with a template. This enables you to control the formatting and output, which lets you build a truly data-driven website. Suppose that you have a website that's mostly an interface to research data. It is easy, then, to create a set of XSLT stylesheets that drive the flow of the site.

Use a stylesheet to display an HTML table of Customers. This stylesheet is depicted in Listing 9.27.

Listing 9.27 A Stylesheet Applied to a SQL Server Template File

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   xmlns:sql="urn:schemas-microsoft-com:xml-sql"
   version="1.0" >

   <xsl:output method="html" version="4.0"/>
   <xsl:template match="/">
     <html>
        <body>
          <xsl:apply-templates />
        </body>
     </html>
   </xsl:template>
   <xsl:template match="Data">
     <table border="1">
        <xsl:for-each select="Customers">
          <tr>
             <td><xsl:value-of select="CustomerID"/></td>
             <td><xsl:value-of select="CompanyName"/></td>
          </tr>
        </xsl:for-each>
     </table>
   </xsl:template>
</xsl:stylesheet>

Save the stylesheet from Listing 9.27 as Customers.xslt in the templates directory with the query templates you have generated throughout this section. You can then edit the sampleparameter.xml template that you created in Listing 9.25 to use a stylesheet for its output.

<?xml version="1.0" encoding="utf-8" ?>
<Data xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="customers.xslt">
 <sql:header>
   <sql:param name="CustomerID">ALFKI</sql:param>
 </sql:header>
 <sql:query>
  exec GetCustomersXML @CustomerID
 </sql:query>
</Data>

The result of executing this query is shown in the following code snippet:

<table border="1" xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<tr>
<td>ALFKI</td>
<td>Alfreds Futterkiste</td>
</tr>
</table>

Creating XML Views

You have seen that you can retrieve XML from SQL Server and query the returned XML document by using the .NET managed classes. You have also seen that you can return transformed XML from SQL Server by specifying XSLT files that reside in a SQL Server virtual directory. Suppose that you want to query the database as an XML document. One way to do this would be to execute a FOR XML clause and return the XML to the client so it can be queried using XPath. But constructing complex FOR XML clauses can be tedious and difficult to maintain.

Yet another way that SQL Server 2000 supports XML is through its representation of the database as an XML document, called an XML view. To provide an XML view, SQL Server uses a mapping between the physical database item and an XML representation of that item. This mapping is provided through annotations to an XDR Schema. SQL Server 2000 supports only XDR Schemas, but you can also use XSD Schemas by installing the SQLXML 3.0 add-on, which is discussed in the section, "SQLXML 3.0."

XML views are used for other technologies within SQL Server. For example, you can use XML views in conjunction with XPath statements to provide XPath querying over the database on the server rather than pulling the data back to the client to be queried. You can also use XML views with Updategrams to perform updates to the database using XML syntax.

The best analogy for an XML view is a database view. A database view does not actually contain data; it only contains pointers to where the data is physically stored and how it is represented. The same is true with an XML view: It simply serves as a set of pointers to where the physical data resides and describes how the XML representation should appear.

There are two ways to structure a schema to develop the mappings between database objects and XML: by using default mappings, and creating explicit mappings using SQL Server 2000 annotations to the XDR Schema.

Using Default Mappings

By default, elements map to a table or view having the same name and attributes map to the columns within that table or view. For example, the database table Customers in the Northwind database would map to an XML element named Customers, and the database column CustomerID in the Customers table would map to an attribute named CustomerID in its XML representation.

Let's walk through a simple example that uses default mappings. You will query the Customers table in Northwind by using XPath. Listing 9.28 shows a simple XDR Schema that does not use annotations.

Listing 9.28 An Example of an XDR Schema Without Annotations

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
    xmlns:dt="urn:schemas-microsoft-com:datatypes"
    xmlns:sql="urn:schemas-microsoft-com:xml-sql">

<ElementType name="Customers" >
  <AttributeType name="CustomerID" />
  <AttributeType name="CompanyName" />
  <AttributeType name="ContactName" />

  <attribute type="CustomerID" />
  <attribute type="CompanyName" />
  <attribute type="ContactName" />
</ElementType>
</Schema>

To test this example, create a virtual name for schemas by following these steps:

1. Open the IIS Virtual Directory Management for SQL Server MMC snap-in.

2. Choose the Northwind virtual directory and open its property sheet.

3. On the Virtual Names tab, click the New button.

4. Enter the virtual name as myschemas, the type as schema, and the path as schemas.

You specified a relative path, so now you need to save the preceding file to the schemas folder in your local path for the virtual directory. The full path on my machine is

D:\projects\New Riders\Chapters\SQL Server\schemas\customers.xdr

You also need to ensure that your virtual directory enables XPath queries. Go to the Settings tab for the virtual directory in the SQL Server MMC snap-in and make sure the Allow XPath option is checked. After you configure the virtual name, you can execute the following XPath query through a URL:

http://localhost/northwind/myschemas/customers.xdr/
Customers[@CustomerID='AROUT']?root=Data

The result of this XPath query is shown in Listing 9.29.

Listing 9.29 Results of a Sample XPath Query Using Default Mappings

<?xml version="1.0" encoding="utf-8" ?>
<Data>
   <Customers CustomerID="AROUT" CompanyName="Around the Horn" 
    ContactName="Thomas Hardy"/>
</Data>

Using Explicit Mappings

Cases can arise when using default mappings simply will not suffice. The table name can have a space in it, so you need to provide an XML element without a space in the name and map that to the table. Or you can have an XPath query that spans multiple tables. A variety of reasons exist for why you can want to tweak the mappings of a schema to the database to provide an XML view that can be queried using XPath.

Take a closer look at how you can use XPath syntax within a query template. To use XPath syntax, you must map the tables and columns using annotations to a schema. The current release of SQL Server only supports XDR schemas; however, you will see later in the section, "SQLXML 3.0," that XSD Schemas are supported as well. The annotations provide control over what the mapping from XML Schemas to the database will look like, but still enable you to stay within the realm of XML and schemas for configuration of that mapping. Table 9.7 lists the annotations provided for XDR schemas and describes each XML component.

Table 9.7 The SQL Server 2000 XDR Annotations

Take a look at the most popular example from the Northwind database, representing Customers and their Orders. You will extend the previous example from Listing 9.19 to use roughly the same hierarchy, making some minor adjustments for further explanation. Listing 9.30 shows the finished XDR Schema.

Listing 9.30 Sample XDR Schema Using Annotations

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
xmlns:dt="urn:schemasmicrosoft-com:datatypes"
 xmlns:sql="urn:schemas-microsoft-com:xml-sql">

   <ElementType name="Order" sql:relation="Orders" sql:key-fields="OrderID">
     <AttributeType name="OrdID" dt:type="id" sql:id-prefix="Orders-" />
     <AttributeType name="ShipMethod" />
     <AttributeType name="ShippedDate" />
     <AttributeType name="ShipName" />

     <attribute type="OrdID" sql:field="OrderID" />
     <attribute type="ShipMethod" sql:field="ShipVia" />
     <attribute type="ShippedDate" sql:field="ShippedDate" />
     <attribute type="ShipName" sql:field="ShipName" />
     <element type="OrderDetail" >
        <sql:relationship
          key-relation="Orders"
          key="OrderID"
          foreign-relation="[Order Details]"
          foreign-key="OrderID" />
     </element>
   </ElementType>

   <ElementType name="Price" sql:relation="[Order Details]" />
   <ElementType name="Qty" sql:relation="[Order Details]" />
   <ElementType name="Disc" sql:relation="[Order Details]" />

   <ElementType name="OrderDetail" sql:relation="[Order Details]" 
    sql:key-fields="OrderID ProductID">
    <AttributeType name="OrdID" dt:type="idref" sql:id-prefix="Orders-" />
    <AttributeType name="ProdID" dt:type="idref" sql:id-prefix="Products-" />

    <attribute type="ProdID" sql:field="ProductID" />
    <attribute type="OrdID" sql:field="OrderID" />
    <element type="Price" sql:field="UnitPrice" />
    <element type="Qty" sql:field="Quantity" />
    <element type="Disc" sql:field="Discount" />
    <element type="Product" sql:relation="Products">
      <sql:relationship
          key-relation="[Order Details]"
          key="ProductID"
          foreign-relation="Products"
          foreign-key="ProductID" />
     </element>
   </ElementType>


   <ElementType name="Product" sql:relation="Products" sql:key-fields="ProductID">
     <AttributeType name="ProductID" dt:type="id" sql:id-prefix="Products-" />
     <AttributeType name="ProdName" />
     <AttributeType name="Category" />
     <AttributeType name="QtyPerUnit" />

     <attribute type="ProductID" />
     <attribute type="ProdName" sql:field="ProductName" />
     <attribute type="Category" sql:field="CategoryID" />
     <attribute type="QtyPerUnit" sql:field="QuantityPerUnit" />
   </ElementType>

   <ElementType name="Customer" sql:relation="Customers">
     <AttributeType name="CustID" dt:type="id" />
     <AttributeType name="CompanyName" />
     <AttributeType name="CustomerOrders"
        dt:type="idrefs"
        sql:id-prefix="Orders-" />

     <attribute type="CustID" sql:field="CustomerID" />
     <attribute type="CompanyName" sql:field="CompanyName" />
     <attribute type="CustomerOrders" sql:relation="Orders"_sql:field="OrderID">
        <sql:relationship
          key-relation="Customers"
          key="CustomerID"
          foreign-relation="Orders"
          foreign-key="CustomerID" />
     </attribute>
     <element type="Order">
        <sql:relationship
          key-relation="Customers"
          key="CustomerID"
          foreign-relation="Orders"
          foreign-key="CustomerID" />
     </element>
   </ElementType>
</Schema>

The most notable portion of the schema is the OrderDetail element type definition. This highlighted section shows how to use several key annotations. For example, you can alias a table name by using the sql:relation annotation, as you did by renaming the Order Details table as OrderDetail. You also aliased column names by using the sql:field annotation to designate to which column in the table the element or attribute belongs. You also used idref attributes to reference id fields from other tables, as seen with the OrdID and ProdID attributes. Finally, this section shows you how to build relationships to other tables to represent a hierarchy using the sql:relation annotation.

If you are not familiar with XDR syntax, you will see the more familiar XSD Schema syntax later in the section, "XML Views Using Annotated XSD Schemas."

Save this template to the schemas directory as ExplicitMapping.xdr and run the following XPath query in your browser:

http://localhost/northwind/myschemas/ExplicitMapping.xdr/Customer/Order 
   [@OrdID='Orders-10308']?root=data

The result of this query is shown in Listing 9.31.

Listing 9.31 Sample XDR Schema Using Annotations

<?xml version="1.0" encoding="utf-8" ?>
<data>
   <Order OrdID="Orders-10308" ShipMethod="3" ShippedDate="1996-09-24T00:00:00"
ShipName="Ana Trujillo Emparedados y helados">
     <OrderDetail>
        <Price>28.8</Price>
        <Qty>1</Qty>
        <Disc>0</Disc>
        <Product ProductID="Products-69" ProdName="Gudbrandsdalsost"
Category="4" QtyPerUnit="10 kg pkg." />
     </OrderDetail>
     <OrderDetail>
        <Price>12</Price>
        <Qty>5</Qty>
        <Disc>0</Disc>
        <Product ProductID="Products-70" ProdName="Outback Lager"
Category="1" QtyPerUnit="24 - 355 ml bottles" />
     </OrderDetail>
   </Order>
</data>

Using XPath Queries

Now that you have defined your schema as a view of the database, you can look further at using XPath with SQL Server. Several benefits exist to using XPath instead of SQL. For example, developers not familiar with SQL, but familiar with XML, can develop complex queries quickly. Because SQL is a relational language, it is sometimes difficult to hierarchically represent data. Using XML, it becomes easy to see hierarchical relationships between tables.

Limitations exist to what can be expressed using XPath in SQL Server, however, because SQL Server only supports a subset of the XPath specification. Table 9.8 shows the unsupported features.

Table 9.8 Unsupported XPath Features in SQL Server

Now that you know what the limitations are, look at a few examples.

One way to issue XPath queries is by providing XPath statements in the URL in a browser. Simply type the following URL in the browser, taking care to refer to an actual order ID for customer ID ANTON:

http://localhost/Northwind/myschemas/ExplicitMapping.xdr/
Customer[@CustID='ANTON']/Order[@OrdID='Orders-10308']

Besides issuing XPath statements in the browser URL, you can also include them in template files. Recall that template files can contain one or more XPath statements and/or one or more SQL statements. The template containing the XPath query is shown in Listing 9.32. Save the template file to your templates directory and adjust the mapping-schema attribute value to match your directory structure.

Listing 9.32 Sample Template File Containing XPath Queries

<?xml version="1.0" encoding="utf-8" ?>
<Data xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <sql:xpath-query mapping-schema="../schemas/ExplicitMapping.xdr">
     Customer/Order[@OrdID='Orders-10308']
   </sql:xpath-query>
</Data>

Because of the current limitations on XPath syntax, it is difficult to write complex XPath statements that take advantage of the node-set querying abilities of XPath. As this technology continues to mature, watch for added support in the form of service packs and add-ons.

Using Updategrams is discussed in depth in the section, "XML Views Using Annotated XSD Schemas."