XML and SQL 2000 (Part 7)

July 25, 2003

We are ready to conclude the discussion on various methods of extracting relational data from SQL Server 2000 in XML format via HTTP. All methods described in previous articles are available through SQL ISAPI extensions to Internet Information Server, implemented as IIS Virtual Directory Management for SQL Server in the release of SQL Server 2000. We have covered all options available on the Settings tab of a virtual directory Properties dialog box, with the exception of the last one - "Allow POST" - which will be the topic of this article.

In order to understand the meaning of this option, we will need to review briefly, the methods used for communication between a Web client and a Web server. In a typical pre-.NET environment (I will describe how Web Services changed this scenario later), this communication took the form of a request, sent from a client computer running a Web browser to a Web server and a response, sent back from the server to the client. The communication followed the rules of the Hypertext Transfer Protocol, where the request from the client was formatted typically as either GET or POST method:

  • GET method is used most frequently - when a user types in the target URL string, clicks on a link to another web page, or selects one of the bookmarks from the list of Favorites. It is also possible to generate a GET request when submitting a form within a web page. The GET method includes a target Web page and additional parameters, which contain details of a client request within the URL string. Since the length of the URL string is limited to 2,048 characters when using Internet Explorer 5.5 or earlier (refer to the Microsoft Knowledge Base article for details), this also imposes a limitation on the total length of the client request. If the list of parameters within the request exceeds this length, which might be the case with some of the options we discussed earlier (such as SQL or template URL queries), you can resort to the POST method, which is not subject to this limitation.

  • POST method is generated when submitting a form within a web page. Parameters of the request are not sent as part of the URL string but are included in the HTTP header. This also makes communication more secure, since the content of a client request is not displayed to a casual observer.

Let's take a look at an example demonstrating use of the POST method to extract relational data in XML format from the Northwind virtual directory we have been using so far. We will start by creating a simple Web page that will generate POST formatted request sent to a virtual directory on our Web server. Note, however, that this Web page should not reside in the same location as the virtual directory created using IIS Virtual Directory Management for SQL server. Instead, you need to use a "standard" virtual directory created using Internet Information Services MMC snap-in. For the sake of our example, let's assume that we will place our web page in the root directory of the default Web Site, which default location is c:\Inetpub\wwwroot. We will call the page PostRequest.html.

Obviously, your web page can be as elaborate as you wish, but we will start by creating a very simple one, containing a single command button labeled "Submit SQL Query." Once you click on it, the page will return the XML document displaying the content of the Shippers table from the Northwind database as a well-formed XML document. The web page will contain the following:

<FORM ACTION="http://WebServer/Northwind" METHOD="POST">
<INPUT TYPE="hidden" NAME="contenttype" VALUE="text/xml">
<INPUT TYPE="hidden" NAME="sql" VALUE="SELECT * FROM Shippers FOR XML AUTO">
<INPUT TYPE="hidden" NAME="root" VALUE="ShippersList">
<INPUT TYPE="SUBMIT" VALUE="Submit SQL Query">

The web page consists of a single form, with Submit button and three hidden fields. Each of the hidden fields - contenttype, sql, and root - corresponds to the parameter used when processing the POST request by SQLISAPI to create the resulting XML document. Each one also contains the value entry, which determines type of the content, sql statement to be executed, and name used as the XML document root, respectively. Note that you should replace the WebServer entry in the URL in the first line of the PostRequest.html to match your Web server name.

In addition to creating the PostRequest.html, you need to also ensure that both "Allow sql=... or template=... URL queries" and "Allow POST" options are selected on the Settings tab of the virtual directory Properties dialog box.

Once you complete the steps outlined above, launch a browser on a client workstation and point it to http://webserver/PostRequest.html. You should see a blank web page containing only a single command button labeled "Submit SQL Query." Once you click on it, shortly after you should see the results of the query similar to the following:

<?xml version="1.0" encoding="utf-8" ?> 
  <ShippersList>
    <Shippers ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831" /> 
    <Shippers ShipperID="2" CompanyName="United Package" Phone="(503) 555-3199" /> 
    <Shippers ShipperID="3" CompanyName="Federal Shipping" Phone="(503) 555-9931" /> 
  </ShippersList>

Obviously, you can be more creative with the types of queries you submit. For example, you can specify the data in which you are interested. To demonstrate this, we will extract information about the shipper, whose Shipper ID matches the value we enter on the web page. In order to accomplish this, we will modify the original PostRequest.html in the following fashion:

<FORM ACTION="http://localhost/Nwind" METHOD="POST">
<B>Enter Shipper ID </B><INPUT TYPE="text" NAME="ShipperID">
<INPUT TYPE="hidden" NAME="contenttype" VALUE="text/xml">
<INPUT TYPE="hidden" NAME="sql" VALUE="SELECT * FROM Shippers WHERE ShipperID = ? FOR XML AUTO">
<INPUT TYPE="hidden" NAME="root" VALUE="ShippersList">
<INPUT TYPE="SUBMIT" VALUE="Submit SQL Query">

Note that we added another input element of type text named ShipperID. This will result in a web page with a single text box labeled "Enter Shipper ID" and the same command button as before. Once you enter an integer value in the text box and click on the "Submit SQL Query" button, the value you typed in will be placed instead of the question mark placeholder in the SQL query and you will see the results in the format (assuming that you entered 1 as the ShipperID value):

<?xml version="1.0" encoding="utf-8" ?> 
  <ShippersList>
    <Shippers ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831" /> 
  </ShippersList>

It is also worth noting that the "Allow POST" option needs to be enabled when using SOAP for communicating with SQL Server via Web Services. This way, you can for example, expose SQL Server 2000 stored procedures as Web Services (although for this purpose you will need release SQLXML 3.0, which you can download from the Microsoft Web site.

We have completed the coverage of options available from the Settings tab of a virtual directory properties dialog box in the IIS Virtual Directory Management for SQL Server. This, however, still does not cover all the possibilities for interaction between SQL 2000 relational databases and XML documents. In the future articles, we will continue this topic with an introduction to bulk loading of XML data and diffgrams.

» See All Articles by Columnist Marcin Policht








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers