XML and SQL 2000 (Part 5) - Page 2

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