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):
- "Allow sql=… or template=… URL queries"
- "Allow posted updategrams"
- "Allow template queries"
- "Allow XPath"
- "Allow POST"
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:
- ShipperID – identity
- CompanyName – nvarchar of the length 40
- Phone – nvarchar of the length 24 (allowing nulls)
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:
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: