SQL Server XML Technology Preview

This Microsoft SQL Server XML technology Preview demonstrates XML capabilities using SQL Server and IIS. It is an IIS ISAPI extension that provides http access to SQL Server and XML data formatting and updating capabilities. Please take special note that this is a preview with unreleased product code, and as such it has not been thoroughly tested in the high load and high stress testing conditions that a heavily loaded web server is likely to generate. Do not expect the level of performance of final, generally available Microsoft products.

More Details
With the appropriate configuration, this sample will allow URL queries like:
http://IISServer/northwind?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO

or allow you to store canned queries (including stored procedure calls) on your IIS server which can be executed with optional parameters from the URL or posted from an HTML form.

The layout of the XML returned can be specified in many ways (including a useful "Auto" mode) and includes the ability to include schema information either in DTD or XML-Data formats.

Install this program using the installation executable provided. Note that IIS must be present on the target machine. This will copy the ISAPI dll and create a menu entry XML Technology Preview for SQL Server which includes links to the documentation, samples and online locations for support and updates (also see below).

After the program is installed, you will need to configure IIS using the provided regxmlss utility menu option (called Registration tool). Assuming you have a SQL Server installed with the sample database Northwind, a useful starting point is to use the regxmlss defaults and provide a SQL Server userid and password and to turn the URL query option on. Note that the SQL Server does not need to be on the same machine for this configuration to work. You can then use a browser on that machine to try your first query, for example:

http://localhost/Northwind?
sql=select+firstname,lastname+from+employees+for+xml+auto

With this running successfully, you'll be able to try the more advanced samples and examples from the documentation.

One special point to note: The virtual root name you choose (e.g. Northwind) can only be used for accessing your SQL data. Any other web files (such as htm or asp files) will not be accessible from this virtual root. This space is reserved for so-called template files which contain 'canned' queries.

Browser/Platform Compatibility
This preview has been tested on NT4/IIS4 and Windows 2000/IIS5 with SQL Server 7.0 and SQL Server 6.5 SP5. Any other combinations are unlikely to work consistently if at all. In particular, Windows 98 and Personal Web Server will not work.

Usage
There is a simple example to demonstrate the power available by combining XML and XSL together. To install this, do the following: Underneath the installation location (normally "Program FilesCommon FilesMicrosoft SharedWeb Server Extensions40SQLXML") you will find a subdirectory demo with three .xml and .xsl files.

First create a virtual root called Northwind using the registration utility (pointing, obviously to the northwind database). Copy all .xml files to the directory that the virtual root (e.g. c:inetpubwwwrootorthwind). Now use IIS to create another virtual root called XSLFiles and an associated directory (e.g. c:inetpubwwwrootxslfiles) and copy all .xsl files out of the demo directory to that location.

Browse the link http://localhost/Northwind/customer.xml to view the Northwind customers and their orders.

Note that this demonstration requires IE5 on the client because of its client-side XSL support. However it is also possible to do server side XSL processing as described in the documentation.

Complete Article