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

SQLXML 3.0

One stride towards advancing SQL Server's XML capabilities is the release of SQLXML 3.0. This add-on, which is available from http://www.microsoft.com/downloads, enhances SQL Server's out-of-the-box XML functionality by providing annotations and support for creating XML views using XSD Schemas, Updategrams, DiffGrams, and a new .NET Managed Provider in the form of SQLXML Managed Classes. SQLXML 3.0 also introduced the concept of client-side XML formatting, allowing a rowset to be formatted as XML on the client rather than on the server, which saves processing time.

This section focuses on creating annotated XSD Schemas and introduces the SQLXML 3.0 managed provider classes.

Improvements to Virtual Directory Management

SQLXML 3.0 introduces a new MMC snap-in that manages virtual directories for SQL Server. This snap-in should be used in place of the snap-in that ships with SQL Server 2000. It provides new capabilities, such as upgrading existing directories to use the version 2 library and managing caching of templates and schemas.

Virtual directories created with the MMC snap-in that ships with SQL Server can be upgraded to use SQLXML 3.0 by using the snap-in that comes with SQLXML 3.0. Simply click the virtual directory that you want to upgrade using the SQLXML 3.0 MMC snap-in and the tabbed dialog appears to manage the virtual directory as before. A new tab, "Upgrade to Version 3", is added to the dialog. Click the "Upgrade to Version 3" tab to upgrade the virtual directory so that it can use the latest features. Figure 9.6 shows the dialog.

Figure 9.6 SQLXML 3.0 enables upgrading existing virtual directories to version 3.

After the virtual directory is upgraded, you can work with SQLXML 3.0's new features.

XML Views Using Annotated XSD Schemas

As mentioned in Chapters 3 and 4, XDR Schemas were implemented prior to the W3C's recommendation release for XSD Schemas. While existing products, such as BizTalk, currently support XDR Schemas, they do not have wide acceptance throughout the developer community, where XSD Schemas are typically favored. Because one of the main goals of XML development is platform-independence and data interchange, it makes sense that SQL Server would support this concept by supporting the W3C's recommendation.

To use XSD Schemas with SQL Server, you must change the namespaces used with the schema to reference the W3C XML Schema namespace (http://www.w3.org/2001/XMLSchema) and the new Microsoft mapping schema namespace (urn:schemas-microsoft-com:mapping-schema).

Default Mappings

Just as with XDR Schemas, XSD schemas can be used with SQL Server 2000 annotations to create explicit mappings of data, or default mappings may be used as well. Listing 9.33 shows an XML view using default mappings where an element is named the same as a table, and attributes directly map to column names within that table.

Listing 9.33 SQL Server 2000 XSD Schema Using Default Mappings

<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

   <xsd:element name="Customers">
     <xsd:complexType>
        <xsd:attribute name="CustomerID" type="xsd:string"/>
        <xsd:attribute name="CompanyName" type="xsd:string"/>
        <xsd:attribute name="ContactName" type="xsd:string"/>
     </xsd:complexType>
   </xsd:element>
</xsd:schema>

Explicit Mappings

Just as with SQL Server 2000 XDR Annotations, the SQLXML 3.0 XSD Annotation supports annotations to provide mappings to the database. Although most of the functionality remains the same, a few changes exist. Table 9.9 indicates the changed or new items that appear in SQLXML 3.0.

Table 9.9 Changes and Additions to SQL Server 2000 Annotations

The XDR Schema in Listing 9.30 will be changed to use XSD Schema notation. Listing 9.34 shows the finished result.

Listing 9.34 XML View Using SQLXML 3.0 Annotations

<xsd:schema xmlns:xsd=http://www.w3.org/2001/XMLSchema 
 xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
   <xsd:annotation>
     <xsd:appinfo>
     <sql:relationship
             name="CustomerOrders"
             parent="Customers"
             parent-key="CustomerID"
             child="Orders"
             child-        <sql:relationship
             name="OrderDetails"
             parent="Orders"
             parent-key="OrderID"
             child="[Order Details]"
             child-key="OrderID" />
     </xsd:appinfo>
   </xsd:annotation>
   <xsd:complexType name="OrderDetailType">
     <xsd:attribute name="OrderID" type="xsd:int" />
     <xsd:attribute name="ProductID" type="xsd:int" />
     <xsd:attribute name="Quantity" type="xsd:decimal" />
   </xsd:complexType>
   <xsd:complexType name="OrderType">
     <xsd:sequence>
        <xsd:element
             name="OrderDetails"
             sql:relation="[Order Details]"
             sql:relationship="OrderDetails"
             type="OrderDetailType" />
     </xsd:sequence>
     <xsd:attribute
             name="OrderID"
             type="xsd:int"
             sql:field="OrderID"
             sql:identity="ignore" />
     <xsd:attribute name="CustomerID" type="xsd:string" />
   </xsd:complexType>
   <xsd:element name="Customer" sql:relation="Customers">
     <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="CustID" type="xsd:string"_sql:field="CustomerID" />
          <xsd:element name="CompName" type="xsd:string" sql:field="CompanyName" />
          <xsd:element name="ContactName" type="xsd:string" />
          <xsd:element
             name="Order"
             type="OrderType"
             sql:relation="Orders"
             sql:relationship="CustomerOrders" />
        </xsd:sequence>
     </xsd:complexType>
   </xsd:element>
</xsd:schema>

To test this XML view, save the schema as CustomersOrders.xsd into the schemas directory that you have been using throughout this chapter. Open a browser window and type the following URL into it: http://localhost/Northwind/myschemas/CustomersOrders.xsd/Customer[CustID='ALFKI']?root=Data.

Notice that, unlike XDR Schemas, XSD Schemas enable the use of named relationships. This helps segment the code more cleanly because relationships can be defined in one area, types in another, and finally, the implementation of types in yet another. Notice that the sql:identity column was used in the example with a value of ignore (you'll see why this value was chosen in the following section, "Updategrams").

XSD Schemas provide a flexible means of providing a static view, known as XML views, of a database that can easily be queried using XPath notation. XML views can also be used in conjunction with other SQL Server XML tools, such as Diffgrams and Updategrams.

Updategrams

As previously mentioned, XML views are also useful in conjunction with Updategrams. Updategrams provide a means to perform INSERT, UPDATE, or DELETE statements using an XML document. The syntax for an Updategram is as follows:

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

The mapping-schema attribute specifies the XML view to use for mapping. If this is omitted, SQL Server defaults to using default mappings where each element corresponds to a table and attributes correspond to the table's columns. The updg:before block represents what the data should look like before the operation, and the updg:after block represents what the data should look like after the operation. The type of operation depends on the structure of the data:

• If data appears in the updg:before block and not in the updg:after block, a DELETE is performed.

• If data appears in the updg:after block and not in the updg:before block, an INSERT is performed.

• If data appears in both the updg:before and updg:after blocks, an UPDATE is performed.

The updg:sync element determines the bounds of transactional scope. Multiple statements can be contained in each updg:sync block, and if one fails, all statements are rolled back. Each updg:sync block is autonomous: If statements in one updg:sync block fail, other updg:sync statements are not affected.

Inserting Data Using Updategrams

To modify data in a database, different considerations tat need to be made that you already take for granted. For example, how do you handle IDENTITY columns in a table? If you want to provide the value for the identity column, you need to first suppress the creation of the IDENTITY column in the database. If you have the server create the value, you need a way to retrieve the value. These are common tasks that you likely already perform with SQL. To perform these tasks using XML, SQL Server provides several annotations that aid in updating and inserting data in a database. Table 9.10 shows the annotations.

Table 9.10 SQL Server Updategram Annotations

Look at an example of how to perform an INSERT into the database strictly using XML. Create a new XML file, insertupdategram.xml, and insert the code from Listing 9.35 into it.

Listing 9.35 Example of an INSERT Updategram

<?xml version="1.0" encoding="utf-8" ?>
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema="../schemas/CustomersOrders.xsd">
 <updg:before>
 </updg:before>
 <updg:after updg:returnid="NewOrderID" >
  <Customer>
     <CustID>VBDNA</CustID>
     <CompName>Kirk Allen Evans Consulting, Inc.</CompName>
     <ContactName>Kirk Allen Evans</ContactName>
     <Order updg:at-identity="NewOrderID" CustomerID="VBDNA">
        <OrderDetails OrderID="NewOrderID" ProductID="3"_Quantity="10.0"/>
     </Order>
  </Customer>
 </updg:after>
</updg:sync>
</ROOT>

We are referencing the schema from Listing 9.35 as the mapping schema. No data is in the updg:before block, and the data in the updg:after block conforms to the mapping schema, so an INSERT is performed. The following operations are performed in this Updategram:

• A new Customer record is created for CustomerID VBDNA.

• A new Order record is created for CustomerID VBDNA. The generated IDENTITY value is stored into a variable, NewOrderID.

• A new Order Details record is created by using the value of the variable NewOrderID.

• The value of the variable NewOrderID is returned.

To test this result, execute the following URL: http://localhost/northwind/mytemplates/insertupdategram.xml.

The record can already exist in your database from past examples. If so, simply go into the database and delete the record, and try the example again.

The output of this Updategram is shown in Figure 9.7.

Figure 9.7 Output of an Updategram.

Deleting Data Using Updategrams

In the previous section, you inserted a new customer, order, and order detail record into the Northwind database. Because this is test data, say that you would like to delete it. Instead of going through each table and deleting the records, wouldn't it be great to delete them all at once? Using Updategrams, this task is simple.

Again, the schema from Listing 9.35 is referenced. The delete Updategram looks similar to the version that performed an INSERT, with one notable exception: The data only appears in the updg:before section and not the updg:after section. Figure 9.8 shows that the previous example created an Orders record with an OrderID of 11085. This value is referenced to delete the created records. Listing 9.36 shows the Updategram.

The OrderID value can be different on your database. Before deleting orders, check the database for clarification on the order ID that is to be deleted before executing this code.

Listing 9.36 Example of a DELETE Updategram

<?xml version="1.0" encoding="utf-8" ?>
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema="../schemas/CustomersOrders.xsd">
 <updg:before>
  <Customer>
     <CustID>VBDNA</CustID>
     <Order OrderID="11085">
        <OrderDetails OrderID="11085"/>
     </Order>
  </Customer>
 </updg:before>
 <updg:after>

 </updg:after>
</updg:sync>
</ROOT>

Save the Updategram as deleteupdategram.xml to the templates directory that you created earlier in this chapter and execute the following URL: http://localhost/northwind/mytemplates/deleteupdategram.xml.

Figure 9.8 A sample Updategram using a DELETE operation.

Diffgrams

Diffgrams follow the same logic as Updategrams, with some syntax differences. Records use both a Before and an After snapshot to determine what action is performed, although the names of the sections are changed. The general Diffgram format is as follows:

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

  [<diffgr:errors>
    ...
  </diffgr:errors>]
</diffgr:diffgram>

Because the use of Diffgrams so closely resembles that of Updategrams, no further explanations of them are given.

The SQLXML Managed Classes

Notice that, throughout this chapter, we have not covered any new managed code—we only covered querying SQL Server and updating it through the URL or templates. Although you can certainly use URLs and templates through the System.Xml classes, SQLXML 3.0 also provides a set of managed classes, referred to as the SQLXML managed classes. These classes make working with SQL Server's different technologies, such as templates and Updategrams, simple and easy to understand.

Because these classes use the same model as a data provider (data providers are discussed at length in Chapter 8, "Database Access With ADO.NET and XML"), they are familiar to use. There are three objects in the SQLXML managed classes object model: SqlXmlAdapter, SqlXmlParameter, and SqlXmlCommand.

SqlXmlAdapter

Similar to the Adapter objects in the System.Data.SqlClient and System.Data.OleDb namespaces, the SqlXmlAdapter class provides the capability to fill a DataSet object and update its datasource. In fact, these are the only two methods it provides:

• Fill()—Fills a DataSet with the contents specified by the SqlXmlCommand object

• Update()—Updates the data source specified by the SqlXmlCommand object.

SqlXmlParameter

The SqlXmlParameter object is equally unexciting, as it only provides a Name and Value property. As seen in Chapter 8, you can use parameters for stored procedures or as variables within a query.

SqlXmlCommand

The "meat" of the SQLXML 3.0 managed classes is the SqlXmlCommand class. It provides methods for retrieving and modifying data in SQL Server strictly through its XML interfaces.

Table 9.11 lists the properties of the SqlXmlCommand object.

Table 9.11 Properties of the SqlXmlCommand Class

In table 9.12, we discuss the values for the CommandType property of the SqlXmlCommand class. The values Template and TemplateFile bear additional explanation. We use the Template value when we are specifying a template within code that is to be executed. If we are executing a template file that resides on the server, then use the value TemplateFile. This is demonstrated in listing 9.37.

Table 9.12 shows the available methods of the SqlXmlCommand class.

Table 9.12 Methods of the SqlXmlCommand Class

Let's walk through an example to see how the managed classes work. We begin by defining the interface for the web form. Listing 9.37 shows the interface code.

Listing 9.37 User Interface Code for SQLXML Managed Classes Demonstration

<%@ Page language="c#" Codebehind="SqlXmlManagedClasses.aspx.cs" 
  AutoEventWireup="false" Inherits="sqlserverxml_cs1.SqlXmlManagedClasses" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
   <HEAD>
     <title>SqlXmlManagedClasses</title>
     <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">
     <meta name="CODE_LANGUAGE" Content="C#">
     <meta name="vs_defaultClientScript" content="JavaScript">
     <meta name="vs_targetSchema" content=      
      _"http://schemas.microsoft.com/intellisense/ie5">
   </HEAD>
   <body MS_POSITIONING="GridLayout">
     <form id="SqlXmlManagedClasses" method="post" runat="server">
        <asp:DataGrid id="CustomersGrid" runat="server" 
         Width="700px"_BackColor="LightGray" BorderColor="Black" 
         Font-Names="Tahoma" Font-Size="Smaller">
          <HeaderStyle ForeColor="White" BackColor="Black"></HeaderStyle>
        </asp:DataGrid>
        <asp:DataGrid id="OrdersGrid" runat="server" 
         Width="700px"_BackColor="LightGray" BorderColor="Black" 
         Font-Names="Tahoma" Font-Size="Smaller">
          <HeaderStyle ForeColor="White"_BackColor="Black"></HeaderStyle>
        </asp:DataGrid>
     </form>
   </body>
</HTML>

Now that we have defined the interface code, take a look at the code-behind code for Listing 9.37. Listing 9.38 shows the code-behind code.

Listing 9.38 Code-Behind Code for SQLXML Managed Classes Demonstration

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Microsoft.Data.SqlXml;

namespace sqlserverxml_cs1
{
   public class SqlXmlManagedClasses : System.Web.UI.Page
   {
     protected System.Web.UI.WebControls.DataGrid CustomersGrid;
     protected System.Web.UI.WebControls.DataGrid OrdersGrid;
     protected System.Web.UI.WebControls.DataGrid OrderDetailsGrid;

     private string ConnectionString = " Provider=SQLOLEDB;User_ID=sa;
     Password=;Initial Catalog=Northwind;Data Source=at1lt-3165-03";
     //Change this location to point to your local path
     private string BasePath = "d:/projects/new riders/chapters/sql server";

     private void Page_Load(object sender, System.EventArgs e)
     {
        SqlXmlCommand command = new SqlXmlCommand(ConnectionString);
        DataSet ds = new DataSet();
        if (!IsPostBack)
        {
          //Execute a stored procedure that returns the data as XML
          command.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.TemplateFile;
          command.CommandText = BasePath + "/templates/sampleselect.xml";
          command.RootTag = "Data";
          SqlXmlParameter param = command.CreateParameter();
          param.Name = "CustomerID";
          param.Value = "AROUT";

          //Fill a DataSet by executing the template
          SqlXmlAdapter adapter = new SqlXmlAdapter(command);
          adapter.Fill(ds);

          //Populate the DataGrids with the XML data
          CustomersGrid.DataSource = new DataView(ds.Tables["Customers"]);
          CustomersGrid.DataBind();
          OrdersGrid.DataSource = new DataView(ds.Tables["Products"]);
          OrdersGrid.DataBind();
        }

     }

     #region Web Form Designer generated code
     override protected void OnInit(EventArgs e)
     {
        //
        // CODEGEN: This call is required by the ASP.NET Web Form        
        // Designer.
        //
        InitializeComponent();
        base.OnInit(e);
     }

     /// <summary>
     /// Required method for Designer support - do not modify
     /// the contents of this method with the code editor.
     /// </summary>
     private void InitializeComponent()
     {
        this.Load += new System.EventHandler(this.Page_Load);

     }
     #endregion
   }
}

The pertinent section is in the Page_Load event code. We executed a template file using the SQLXML managed classes, loaded a DataSet, and displayed the contents of the DataGrid in the browser.

As you can see, the SqlXmlCommand is the workhorse of the SQLXML managed classes object model. By using this set of classes, it's easy to execute templates, XPath statements, and FOR XML queries using the object model.

Exposing SQL Server as a Web Service

Another new feature in SQLXML 3.0 is the capability to expose SQL Server 2000 as a web service. This capability enables you to send SOAP HTTP requests to the server to execute stored procedures, user-defined functions, and templates. These web services can then be consumed using the SOAP Toolkit or by leveraging .NET's web service capabilities (these are discussed in detail in chapter 11).

Let's walk through the steps necessary to configure SQL Server as a web service. We will continue to use the Northwind virtual directory created earlier in the section, "Configuring SQL XML Support in IIS". To configure SQL Server as a web service:

• Open the Cofigure IIS Support MMC snap-in that was installed with SQLXML 3.0.

• Locate the Northwind virtual directory. Right-click this virtual directory and select Properties.

• Click the Virtual Names tab.

• Enter a name for the new web service virtual directory. Our example will use mysoap.

• Choose soap in the type drop-down list. This tells SQL Server that this virtual name defines a SOAP web service directory.

• Enter a path to a valid directory on the database server. Our example uses d:\projects\sql soap.

• Enter a web service name for the virtual name. Our example uses sqlsoap as the web service name.

• Enter a domain name for the web service. This can be the name of your web server.

• Click the Save button. The configure button is now enabled. Click the configure button. A new property window appears.

• In the SOAP Virtual Name Configuration window, choose the type of mapping (template or stored procedure). Our example will execute a template, so choose the Template option.

• Enter a method name for this operation. Our example uses GetSampleSelect as the method name.

• In the SP/Template read-only textbox, choose the ellipsis button to locate either the stored procedure or template file. We are using the sampleselect.xml template file from listing 9.22.

• Click Save to save the new method. Click OK to exit the property window.

Behind the scenes, the configuration of the SOAP virtual name actually created a WSDL file in the path specified. After completing the above steps for the sampleselect.xml template file, a new file was created as D:\projects\sql soap\sqlsoap.wsdl.

We will create a small test harness to call our sample web service. In Visual Studio .NET, click the References node in the Solution Explorer window. Right-click the References node and choose Add Web Reference. A dialog window is displayed, prompting for the location of the WSDL file.

Configuring our sample created a virtual name called mysoap in the Northwind virtual directory as well as its corresponding WSDL file. To access the WSDL for the web service, we enter the full URL with wsdl as a single querystring in the address bar:

http://localhost/Northwind/mysoap?wsdl

Click the Add Reference button to add the web reference. Now that the web reference is configured and the web service enabled, we can now develop a quick client application to test our service.

Create a new web form called soapTest, and enter the following code in soapTest.aspx:

<%@ Page language="c#" Codebehind="soapTest.aspx.cs" 
 AutoEventWireup="false" Inherits="WebApplication1.soapTest" %>
<HTML>
   <HEAD>
     <title>Calling a SQL Server Web Service</title>
   </HEAD>

   <body MS_POSITIONING="GridLayout">
     Results from SQL Web Service Call:
     <br/>
     <div id="templateHolder" runat="server">
     </div>
   </body>
</HTML>

This code simply creates an HTML server DIV control called templateHolder. We then look at the code-behind for soapTest.aspx, depicted in Listing 9.38:

Listing 9.38 Code-Behind Code for SQLXML Web Service Demonstration

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;


namespace WebApplication1
{
   public class soapTest : System.Web.UI.Page
   {
     protected System.Web.UI.HtmlControls.HtmlGenericControl templateHolder;

     private void Page_Load(object sender, System.EventArgs e)
     {
        System.Xml.XmlElement returnVal;

        localhost.sqlsoap proxy = new localhost.sqlsoap();

        object[] results;
        results = proxy.GetSampleSelect("ANTON");
        returnVal = (System.Xml.XmlElement)results[0];
        templateHolder.InnerText = returnVal.OuterXml;
     }

     #region Web Form Designer generated code
     override protected void OnInit(EventArgs e)
     {
        //
        // CODEGEN: This call is required by the ASP.NET Web Form
        // Designer.
        //
        InitializeComponent();
        base.OnInit(e);
     }

     /// <summary>
     /// Required method for Designer support - do not modify
     /// the contents of this method with the code editor.
     /// </summary>
     private void InitializeComponent()
     {
        this.Load += new System.EventHandler(this.Page_Load);

     }
     #endregion
   }
}

When run, this sample emits an XML representation similar to that found in Listing 9.23.