Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 28, 2003

XML and SQL 2000 (Part 5) - Page 2

By Marcin Policht

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=
    <updg:sync updg:returnid="ShippersRow">
        <updg:after updg:returnid="ShipperID">
          <Shippers updg:at-identity="ShipperID"
          CompanyName="Speedy Gonzalez"
          Phone="(503) 555-9934"

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">

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


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM