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.