XML Updategrams

The new XML for SQL Web Release supports XML documents called updategrams, which let you use XML across HTTP to update your SQL Server 2000 databases. Updategrams contain special tags that work with SQL Server's IIS support to insert, update, and delete rows in a SQL Server table. Middle-tier agents such as Web applications and n-tier application components that post changes to the database server typically generate the XML updategrams and send them to the SQL Server system across HTTP. You can use updategrams much as you use XML templates, which let you execute SQL statements to update SQL Server databases. However, with updategrams, you use only XML documents to update SQL Server databases—you don't need SQL statements. (For more information about XML templates, see Dan Fox, "ADO and XML," December 2000, and Paul Burke, "XML and SQL Server 2000," May 2000.) Let's walk through how you configure SQL Server 2000's IIS support to enable updategrams, then look at sample updategrams that insert, update, and delete information in a SQL Server database.

Configuring Updategram Support

To use updategrams, you must install the XML for SQL Web Release, which you can download from the Microsoft Web site at http://msdn.microsoft.com/downloads/default.asp. (For more information about the Web Release, see the sidebar "Installing the XML for SQL Web Release," page 67.) After you've installed the Web Release, you can use the IIS Virtual Directory Management for SQL Server administrative tool to configure support for updategrams. You run the administrative tool from Start, Programs, SQL Server, Configure SQL XML Support in IIS.

IIS Virtual Directory Management for SQL Server lets you define and register a new virtual directory on the computer that is running IIS. This new IIS virtual directory defines a connection between IIS and an instance of SQL Server. After starting the IIS Virtual Directory Management for SQL Server tool, right-click the Default Web Site entry and select New Virtual Directory from the pop-up menu. Figure 1, page 66, shows the resulting SQLXMLObjects Properties dialog box.

On the General tab, specify the name of the IIS Virtual Directory and the path to the files that will reside on that virtual directory. Figure 1 shows the Virtual Directory Name set to SQLXMLObjects and the path set to c:inetpubwwwrootSQLXMLObjects. Now, click the Security tab to display the security configuration dialog box that Figure 2 shows.

The Security tab lets you set the authentication options that the SQL Server XML SQLISAPI DLL uses to connect to SQL Server. This DLL passes incoming HTTP requests to the OLE DB Provider for XML, which IIS uses to connect to SQL Server. You have three basic security options for Web-client authentication: Anonymous authentication, Basic authentication, and Integrated Windows authentication. Selecting the Always Log on as radio button, as Figure 2 shows, sets up Anonymous access, which is typically the best choice for Internet access to SQL Server. Anonymous access uses the same security setting—either a shared Windows NT login or a SQL Server login—for the entire virtual directory. Basic authentication specifies a fixed SQL Server login ID to use for access, but the Web client passes this login across the network in clear text. Thus, Basic authentication isn't suited for Internet connections unless you associate it with a Secure Sockets Layer (SSL) connection. Integrated Windows authentication is good for intranet implementations in which the Windows network ID is available, but Integrated Windows security isn't typically useful for Internet connections because you can't pass NT authentication information across the Internet outside your domain. After selecting the type of authentication you want to use, click the Data Source tab to display the dialog box that Figure 3 shows.

You use the Data Source tab to specify the SQL Server system that you want to service the virtual directory. If IIS is running on the same system as SQL Server, you can specify the value (local). Otherwise, you must enter the name of a SQL Server instance. Figure 3 shows the virtual directory associated with the SQL Server system teca4 and shows the default database set to pubs. Now, click the Settings tab to display the IIS Virtual Directory Management dialog box that Figure 4 shows.

The Settings tab lets you control the type of SQL Server access permitted from the virtual directory. To allow updategram execution, you must select the Allow template queries check box, which lets users either execute SQL statements stored in XML template files or run XML updategrams located in the server's virtual templates directory. Notice in Figure 4 that the Allow posted updategrams option, which you might expect to appear selected, is clear. If you select this option, you're allowing the execution of only updategrams and prohibiting the execution of standard XML template queries. Clicking the Virtual Names tab displays the dialog box that Figure 5 shows.

The Virtual Names tab lets you create new virtual directories that the Web client uses to locate XML templates, XML schema, and XPath queries. To create a new virtual name, select from the Type drop-down box the object type to apply to the path, then click New, which lets you enter the virtual directory name followed by the path to the system on which the files will be stored. Figure 5 shows that you'll store templates and updategrams in a virtual directory called templates (you don't need a separate virtual directory for updategrams; you store them along with templates or schemas) and that you'll store the physical template files in the c:inetpubwwwrootSQLXMLObjectstemplates subdirectory.

Updategrams in Action

After adding the required XML for SQL Server settings to IIS, you're ready to use updategrams to update your SQL Server database. However, before using updategrams to modify data in the Pubs database, let's first build a sample table so that you won't affect any existing user tables in Pubs.

First, execute the XML template that Listing 1 shows to drop and recreate the sample Department table that the updategrams will use. You can execute this template, which I called createdept.xml, by simply passing the name and virtual directory to IIS as part of a URL, as the following example shows:

http://teca4/sqlxmlobjects/template/createdept.xml

After you've created the sample table, you're ready to use an updategram to insert data into the table.

Using updategrams to insert data. Updategrams contain special Sync, Before, and After tags that tell IIS how to process the XML document's contents. One updategram can send multiple database actions to the server. The Sync tag identifies a transaction; SQL Server considers all actions within a Sync section as one transaction. You use Before and After tags to specify the different database actions you want SQL Server to perform. Before tags specify what the data looks like before a change, whereas After tags specify what the data looks like after a change. To insert a row, you use only the After tag. To delete a row, you use only the Before tag. And to update a row, you use the Before tag to identify the data that needs updating, then the After tag to identify the new values.

Listing 2 shows an updategram called insertdept.xml, which inserts a row into the Department table that you created earlier. In this example, the first updg:sync tag marks the beginning of the transaction. The updategram uses only updg:after tags, telling SQL Server to insert the after image of the data. The element between updg:after tags identifies the table you want to update and the columns to which you want to add the row. In this example, the updategram instructs SQL Server to insert the new row into the Department table's Dep_ID and Dep_Name columns. Although this example illustrates inserting a single row, you could use multiple department elements with the updg:after tags to insert multiple rows. The closing updg:sync tag marks the end of the transaction. You can also use a URL to execute this updategram, as the following example shows:

http://teca4/sqlxmlobjects/template/insertdept.xml

Using updategrams to update data. Listing 3 shows an updategram called updatedept.xml, which uses both the Before and After tags to update a row in the Department table. This updategram contains both updg:before and updg:after tags, which tell SQL Server to perform an update action. The updg:before tags specify the row in the target table that you want to update. This example identifies for the update action the Dep_ID column row that has a value of 1111. After the closing updg:before tag, this example uses updg:after tags to identify the updated values for the row. As with the insert updategram, the element within the updg:after tags specifies the table and column names for the update. This example instructs SQL Server to update the Dep_Name column with the new value of "Update Department of XML." To execute this updategram, you can use a URL like the following:

http://teca4/sqlxmlobjects/template/updatedept.xml

Using updategrams to delete data. Listing 4 shows an updategram called deletedept.xml, which uses only updg:before tags to delete a row from a table. The first updg:sync tag marks the beginning of the transaction, then updg:before tags enclose the elements that specify the row for SQL Server to delete. This example identifies for deletion the Department table row that contains a value of 1111 in the Dep_ID column. To execute this updategram, you can use a URL like the following:

http://teca4/sqlxmlobjects/template/deletedept.xml

Updategrams, available only in the XML for SQL Web Release, continue to raise the bar for SQL Server and Web integration. Add flexibility and power to your XML and SQL Server applications by using updategrams to update SQL Server databases based on the contents of XML documents.

This article is adapted from the SQL Server 2000 Developer's Guide (Osborne/McGraw-Hill), by Michael Otey and Paul Conte.