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:Inetpubwwwroot. 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.
