XML and SQL 2000 (Part 5)
June 27, 2003
In this article, we will continue exploring different ways of accessing SQL Server data in the XML format via HTTP. As we explained in the previous article, this type of functionality becomes available after creating a virtual directory corresponding to the target database using IIS Virtual Directory Management for SQL server MMC snap-in. The Properties dialog box for the virtual directory created in such fashion offers the following options for data access (configurable from the Settings tab):
Since we have already covered the first option, now we will look into the next one labeled "Allow posted updategrams". Note that this option is more restrictive than the first one (and automatically disabled when the first one is selected), since it limits the type of URL-based strings accepted by the virtual directory to XML updategrams only (and does not allow standard SQL queries or XPath queries). However, due to the fact that this option allows users to attempt running unrestricted modification statements against the target database, you should still consider its use only in development or test environments (obviously any statement run this way is still a subject to restrictions placed on the account used to access the database).
While the sql and template queries covered in the previous article allow only extracting data, updategrams provide a way to modify it. However, unlike the queries, updategrams do not use standard T-SQL data modification statements, but instead, accomplish their goal by presenting data values before and after the change. When the updategram is executed against the target database, the "after" value is inserted in place of the "before" value. Deleting one or more values can be accomplished simply by setting their respective "after" value to null. Similarly, to insert new values, you need to use empty "before" values.
Even though the syntax used by updategrams might initially look confusing, it is fairly straightforward. Since updategrams are well-formed XML documents, they consist of standard XML elements: predicates, elements and attributes. The initial predicate is followed by the root element and a namespace (xml-updategram) declaration. The sync tags not only section off the before and after elements but also indicate which portion of the XML document gets translated into a single transaction (which means that updates within this element either all succeed or all fail - including situations where a single sync element includes multiple before and after elements). Before and after elements are marked with the tags labeled, respectively, before and after.
Let's take a look at the process of creating of a new row in the Shippers table of Northwind database using updategrams. The table has three columns:
The only column that requires special attention is the first one. Since its value is assigned automatically (due to its identity nature), you might not know ahead of time what this value will be. This information might be required when updating or inserting rows in related tables.. The way to solve this problem is to use an at-identity attribute, which you can subsequently reference throughout the sync element. Here is an example showing creation of such attribute within the updategram that inserts a row of data into the Shippers table:
<?xml version="1.0"?> <ShippersInsert xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync updg:returnid="ShippersRow"> <updg:before> </updg:before> <updg:after> <Shippers updg:at-identity="ShipperID" CompanyName="Speedy Gonzalez" Phone="(503) 555-9934" /> </updg:after> </updg:sync> </ShippersInsert>
This way, if you needed to update a related table with the value of the ShipperID column, you could simply refer to it using ShipperID name.
The updategram provided above is the most straightforward example, in which the top-level element within the before and after elements is named after the table to be updated, while the attributes correspond to column names within this table. You can use subelements instead of attributes if you prefer this type of notation. This would change the Shippers subelement from the example above to the following format:
<Shippers updg:at-identity="ShipperID" <CompanyName>"Speedy Gonzalez"</CompanyName> <Phone>"(503) 555-9934"</Phone> />