SQLXML 3.0
| only for RuBoard |
One stride towards advancing SQL Server's XML capabilities is the release of SQLXML 3.0. This add-on, which is available from 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 (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
| Change or Addition | Description | Comparative in SQL Server 2000 XDR Annotations |
|---|---|---|
| sql:mapped | Enables exclusion of schema items from the output. Literal values are , 1 , true , and false , where = false and 1 = true . | map-field |
| sql:relationship | Defines relationships between elements using attributes parent, parent-key , child , and child-key . | key-relation , foreign-relation , key , foreign-key |
| sql:encode | Creates a URI to be used with BLOB data. Literal values are url or default. | url-encode |
| sql:inverse | Inverses the relationship defined using sql:relationship for use with Updategrams. Literal values are , 1 , true , and false , where = false and 1 = true . | N/A |
| sql:hide | Hides the element from the output. Literal values are , 1 , true , and false , where = false and 1 = true . | N/A |
| sql:identity | Specifies how IDENTITY columns in the database are updated.Values are ignore and useValue . | N/A |
| sql:guid | Indicates how GUIDs are to be handled for diffgrams. Literal values are generate and useValue . | N/A |
| sql:max-depth | Defines a maximum depth for recursive relationships. | N/A |
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-
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
| Annotation | Description |
|---|---|
| updg:id | Enables linking of rows in the updg:before and updg:after blocks when multiple rows are being modified. |
| updg:at-identity | Stores the value of a newly inserted IDENTITY column for use in subsequent tasks. |
| updg:guid | Generates a uniqueidentifier value. |
| updg:returned | Returns the value created for an IDENTITY or uniqueidentifier column. |
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 IDEN TITY 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.
Figure 9.8. A sample Updategram using a DELETE operation.
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 .
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 codewe 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
| Property | Description |
|---|---|
| BasePath | Returns the directory path associated with a virtual name, used to resolve relative paths between different virtual name types (schema, dbobject , or template). |
| ClientSideXml | A Boolean that indicates if XML transformation should occur on the client. |
| CommandStream | The command stream to execute, such as from a memory stream or file stream. |
| CommandText | The text to execute on the server. |
| CommandType | One of the SqlXmlCommandType enumerated values, this property indicates the type of command being executed. When used in conjunction with the CommandStream property, acceptable values are Template , TemplateFile , UpdateGram , and DiffGram . |
| Namespaces | Enables XPath queries that use namespaces. |
| OutputEncoding | Specifies the encoding of the output resulting from executing the command. |
| RootTag | Specifies the root element used for the output. |
| SchemaPath | The path to the schema file, either absolute or relative. |
| XslPath | The path to the XSLT file, either absolute or relative. |
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
| Method | Description |
|---|---|
| ClearParameters | Clears the SqlXmlParameter objects for the command object. |
| CreateParameter | Creates a SqlXmlParameter object. |
| ExecuteNonQuery | Executes a command that does not return a result, such as an INSERT command. |
| ExecuteStream | Executes the command and returns the results in a Stream object. |
| ExecuteToStream | Executes the command and writes the results to the specified Stream object. |
| ExecuteXmlReader | Executes the command and returns the results in an XmlReader object. |
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/
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.
| only for RuBoard |
EAN: N/A
Pages: 184