Using XML Updategrams

In addition to the template and URL techniques illustrated previously, SQL Server 2000 provides special support for Internet updategrams: template queries that insert, update, and delete rows based on special XML elements.

Before you can use updategrams, download and install the XML for SQL Server 2000 Web Release 1 from http://msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/MSDN-FILES/027/001/554/msdncompositedoc.xml. You also need to have installed Microsoft XML Parser version 3, configured an IIS virtual directory for use with SQL Server 2000, and checked the Allow Template Queries option on the Settings tab of the IIS Virtual Directory Management for SQL Server tool for that directory.

The updategram elements (note the use of the updg namespace declared in the root element) are as follows :

• A document root element of any name , having the namespace attribute xmlns:updg="urn:schemas-microsoft-com:xml-updategram" .

• updg:sync ” Like the keyword TRANSACTION, the statements enclosed by updg:sync are considered a transaction in the template query. updg:sync is the parent element of updg:before and updg:after .

• updg:before ” When used in a transaction by itself, rows specified as its child elements are deleted. When used with updg:after , it identifies the rows to be updated by updg:after .

• updg:after ” When used in a transaction by itself, the rows specified as its child elements are inserted. When used with updg:before , it updates the rows identified in updg:before .

Listing 41.14 shows template code that uses an updategram to insert a row in the Shippers table. The code is fairly straightforward: Specify the row you want to insert as a child element of updg:after using either attribute or element-centric XML-to-table mapping. This example specifies the target row using attribute-centric mapping. Save the following code to your Template directory as an .xml file and test it in your browser.

Listing 41.14 Inserting a Row in Shippers Using an Updategram

 <updategramRoot xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync>        <updg:after>               <Shippers CompanyName='IShipIt' Phone='555-1212'/>        </updg:after> </updg:sync> </updategramRoot> 

Deletes work the same way except that the rows that are to be deleted are specified under updg:before . Listing 41.15 attempts to delete a row from Shippers. This example specifies the target row using element-centric mapping.

Listing 41.15 Deleting a Row in Shippers Using an Updategram

 <updategramRoot xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync>        <updg:before>               <Shippers><ShipperID>1</ShipperID></Shippers>        </updg:before> </updg:sync> </updategramRoot> 

Notice the error message that is returned to the browser. It too is a well- formed XML document containing special tags (beginning with the character sequence <? and ending with ?> ) called processing instructions that indicate to the calling program any errors that might have occurred.

The final example (shown in Listing 41.16) is an updategram that updates the ContactName field in a row in Customers. It first identifies the rows to be updated in updg:before . All matching rows (using attribute-centric mapping) will be updated. New values for the columns in the matching rows are specified in the attributes of the Customer elements in updg:after .

Listing 41.16 Updating a Row in Customers Using an Updategram

 <updategramRoot xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync>        <updg:before>               <Customers CustomerID="ALFKI"/>        </updg:before>        <updg:after>               <Customers CustomerID="ALFKI" ContactName="Alien Life Form"/>        </updg:after> </updg:sync> </updategramRoot>