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