In part 5 of his XML and SQL 2000 series Marcin Policht continues exploring different ways of accessing SQL Server data in the XML format via HTTP. This month he delves into 'Allow posted updategrams.'
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:
The value of at-identity attribute can also be returned to the browser after
successful modification, providing you used the returnid attribute in the after
element, as in the slightly modified version of our original updategram:
In our case, this would result in the following XML document
being displayed in the browser window, after the updategram specified above
completes (assuming that the Shippers table in your Northwind database contains
only three original entries:
<?xml version="1.0" ?>
If you do not use the returnid attribute, the results of successful
update would be displayed in the following format:
<?xml version="1.0" ?>
<ShippersInsert xmlns:updg="urn:schemas-microsoft-com:xml-updategram" />
Obviously, the most important question that remains unanswered is how to
execute updategrams. As we demonstrated in the previous article, you can insert
the content of the XML document in the Address box of the browser after the
following URL string (the webserver entry needs to be replaced with the actual
name of the Web Server hosting the Northwind virtual directory):
which in our case would produce (note that entire string
should be typed as a single line in the Address box of the browser)
<?xml version="1.0" ?>
<Shippers updg:at-identity="ShipperID" CompanyName="Speedy Gonzalez" Phone="(503) 555-9934"/>
For this method to succeed, you need to ensure that Northwind virtual
directory exists and "Allow posted updategrams" is selected on the
Settings tab of this virtual directory Properties dialog box. Note, however,
that you do not need to have the first option ("Allow sql=... or
template=... URL queries") selected.
This approach has two major drawbacks - the first one is inconvenience, the
second, already mentioned before, is increased security concern, since a user
can attempt any type of modification statement, and with sufficient knowledge
of the target database and lack of proper protection, this might result in
An approach that is much more reasonable is to save the content of the XML
document containing datagram on the web server in the target virtual directory
as a template. In our example, we will save it as InsertShippers.xml file in
the root of the Northwind folder (you can find the actual location of this
folder by checking the Local Path section on the General tab of the Northwind
virtual directory Properties dialog box). Next, you should assign a virtual
name of type template to it. This virtual name will be used when referring to
the template from the browser. To accomplish this, switch to the Virtual Names
tab of the Properties dialog box and click on the New button. This will display
the Virtual Name Configuration dialog box. In the Virtual name text box, type
in InsertShippers (note that this name is arbitrary and does not have to match
the name of the XML document), in the Type list box select Template, and make
sure that Path contains the full path to the XML file we just created. Finally,
click on the Save button and close the dialog box. You also need to ensure that
"Allow template queries" option on the Settings tab is checked on.
At this point, your users can update the Shippers table with the new entry
by pointing their browsers to:
Obviously, typically users will need to be able to specify values of
individual parameters when running updates. This is done by appending to the
URL string specified above individual entries consisting of Parameter
Names=Parameter Value pairs separated by single ampersand. In our case, the
following URL string:
http://webserver/Northwind/InsertShippers?CompanyName="The RoadRunner"&Phone="(503) 555-9934"
would result in inserting additional row to the Shippers
table with the appropriate values for CompanyName and Phone columns.
As you can see, XML not only provides a way to query SQL databases via HTTP,
but also allows modifying their contents. In the next article, we will continue
covering methods involving use of XPath for accessing SQL database via HTTP.
See All Articles by Columnist Marcin Policht