XML and SQL 2000 (Part 5)

Friday Jun 27th 2003 by Marcin Policht
Share:

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:

<?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>
/>

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:

<?xml version="1.0"?>
<ShippersInsert xmlns:updg=
  "urn:schemas-microsoft-com:xml-updategram">
    <updg:sync updg:returnid="ShippersRow">
        <updg:before>
        </updg:before>
        <updg:after updg:returnid="ShipperID">
          <Shippers updg:at-identity="ShipperID"
          CompanyName="Speedy Gonzalez"
          Phone="(503) 555-9934"
          />
        </updg:after>
    </updg:sync>
</ShippersInsert>

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" ?> 
<ShippersInsert xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
	<returnid>
		<ShipperID>4</ShipperID> 
	</returnid>
</ShippersInsert>

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):

http://webserver/Northwind/?template=

which in our case would produce (note that entire string should be typed as a single line in the Address box of the browser)

http://webserver/Northwind/?template=
<?xml version="1.0" ?>
<ShippersInsert xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync updg:returnid="ShippersRow">
<updg:before></updg:before>
<updg:after updg:returnid="ShipperID">
<Shippers updg:at-identity="ShipperID" CompanyName="Speedy Gonzalez" Phone="(503) 555-9934"/>
</updg:after>
</updg:sync>
</ShippersInsert>

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 unauthorized changes.

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:

http://webserver/Northwind/InsertShippers

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved