XML SQL Server ISAPI Extension

[Previous] [Next]

The SQL Server extension allows you to send a SQL query to a SQL Server 6.5 or 7.0 database through IIS in the HTTP query string of the request and get the data back as XML. The extension will give you a preview of the functionality that will be in SQL Server 2000. You can download this tool from the Microsoft Web site at http://msdn.microsoft.com/xml/articles/xmlsql/sqlxml_prev.asp, where you can find the link Download Microsoft SQL Server XML Technology Preview. Right-click this link and choose Save Target As from the context menu to open the Save As dialog box. You will see the Sqlxmlsetup.exe file in the File Name box. Save this file to the local drive of a server running IIS. To install this extension, follow these steps:

1. Run Sqlxmlsetup.exe. The ISAPI DLL will be copied to your server and a menu entry named XML Technology Preview for SQL Server will be created.
2. Choose Programs from the Start menu, and then choose XML Technology Preview for SQL Server, and then Registration Tool. This will open a vrootmgt MMC snap-in that can be used to set up a SQL database so that the database can be accessed directly through the Web.
3. You will need to create a virtual root on the IIS Web server using the vrootmgt MMC snap-in. As an example of how this works, right click on the Default Web Site, choose New, and then choose Virtual Directory to open the New Virtual Directory Properties window, as shown in Figure 15-1.



Figure 15-1. The New Virtual Directory Properties window.

4. In the General tab, change the default name of the virtual directory to a name such as Northwind and specify a local path to the actual directory that contains the files you want to make accessible through this virtual directory.
5. Click the Security tab. You can choose any of the security mechanisms that are appropriate for your server, including Windows Integrated security and SQL Server account. Select a security mechanism that will give you access to the Northwind Traders database on a SQL Server database that you have access to. If you select the Always Log In As option, you must supply a user ID that has access to the Northwind Traders database.
6. Next select the Datasource tab, choose the correct data source, and pick the Northwind Traders database.
7. Select the Setting tab, and then select Allow URL Queries.
8. Click OK to close the New Virtual Directory Properties window.

Accessing SQL Server

You should now be able to access SQL Server in a URL that specifies HTTP as the protocol. Place the following query into the navigation bar in Internet Explorer 5 and get back the appropriate results:

 http://localhost/northwind?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO 

Figure 15-2 shows what this query returns.

Figure 15-2. The XML data returned from the query.

As you can see, this ISAPI extension allows you to rapidly and easily retrieve data from a SQL Server database. The format of the XML data can be specified in several ways by using the FOR XML clause in the SELECT statement. The FOR XML clause can specify one of the three modes: AUTO, RAW, and EXPLICIT. In Figure 152, we used the AUTO mode to allow the ISAPI extension to format the data as a nested tree. The RAW mode takes the query result and transforms each row in the result set into an XML element with a generic row identifier as the element tag. Figure 15-3 shows what the results would look like using the RAW mode.

Figure 15-3. Using the RAW mode in the query.

The EXPLICIT mode allows you to define the shape of the XML tree that is returned.

Using URLs to Execute Queries

You can use a URL and SQL statements to access SQL Server and execute stored procedures. Besides SQL statements, you can also specify templates using a URL. A template is a SQL query string formatted as an XML document. It contains one or more SQL statements. The general format for the query strings are as follows:

 http://NameOfIISServer/  NameOfVirtualRoot?SQL=SQLQueryString| template=XMLTemplate]  [&param=value[&param=value]…] 

or

 http://NameOfIISServer/NameOfVirtualRoot [/filepath]/  filename.xml [?param=value[&param=value]…] 

If a SQL statement is used, you can use the FOR XML clause and specify one of the three modes mentioned above.

The param value in the above query string is a parameter or a keyword. Keywords can be of three types: contenttype, outputencoding, and _charset_. The contenttype keyword describes the content type of the document that will be returned. The content type can be images such as JPEG and text. The contenttype value will become part of the HTTP header that is returned. The default is text/XML. If you are returning XHTML, you should set conenttype to text/html. If you do not want the browser to perform any formatting, you can use text/plain. For images and other SQL Server binary large object (BLOB) fields, you can use one of the registered MIME types. The registered MIME types can be found at ftp://ftp.isi.edu/in-notes/iana/assignments/media-types/media-types.

The outputencoding keyword is the character set that will be used for the returned document. The default is UTF-8. The _charset_ keyword is the character set for decoding the parameters that are passed in. The default is also UTF-8.

When writing out the queries you must use the defaults for HTML query strings. For example, we used the plus sign (+) for spaces. You will need to use a percentage sign (%) followed by the hex value for the following characters: /, ?, %, #, and &.

You can also specify an XSL file to transform the XML data, as shown in the following URL:

 http://localhost/northwind?sql=SELECT+CompanyName,+ContactName +FROM+ Customers+FOR+XML+AUTO&xsl=customer.xsl&contenttype=text/html 

The file Customer.xsl can be placed in any subdirectory that is part of the virtual root subdirectory tree. The code for the XSL file would look as follows:

 <?xml version="1.0" encoding="ISO-8859-1" ?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"> <xsl:template match = "*"> <xsl:apply-templates /> </xsl:template> <xsl:template match = "Customers"> <TR> <TD><xsl:value-of select = "@CompanyName" /></TD> <TD><B><xsl:value-of select = "@ContactName" /></B></TD> </TR> </xsl:template> <xsl:template match = "/"> <HTML> <HEAD> <STYLE>th { background-color: #CCCCCC }</STYLE> </HEAD> <BODY> <TABLE border="1" style="width:300;"> <TR><TH colspan="2">Customers</TH></TR> <TR><TH >CompanyName</TH><TH>Contact Name</TH></TR> <xsl:apply-templates select = "root" /> </TABLE> </BODY> </HTML> </xsl:template> </xsl:stylesheet> 

This document will look as shown in figure 15-4.

Figure 15-4. The transformed XML data.

You can execute a stored procedure using the EXECUTE command. For example, the Northwind Traders SQL Server database comes with a stored procedure called CustOrderHist. The stored procedure looks as follows:

 CREATE PROCEDURE CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total = SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName 

This stored procedure takes one parameter, the ID of the customer. To execute this query we can use the following HTTP query string:

 http://localhost/northwind?sql=EXECUTE+CustOrderHist+ "ALFKI"+FOR+XML+AUTO 

This query gets the order history for the customer with an ID of ALFKI. The results of this query are shown in Figure 15-5.

Figure 15-5. Sales for customer with ID ALFKI.

Using the SQL ISAPI Extension to Update Data

You can also update data to a database using the SQL ISAPI extension. To perform an update you must create an update gram. An update gram is a template that is sent in an HTML query string. The general format of an update gram is shown below:

 <sql:sync xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:before> <TABLENAME [sql:id="value"] col="value" col="value".../> </sql:before> <sql:after> <TABLENAME [sql:id="value"] [sql:at-identity="value"] col="value" col="value".../> </sql:after> </sql:sync> 

Using this format, you can perform inserts, updates, and deletes. When performing an insert you would leave out the before element, when performing a delete you would leave out the after element, and when performing an update you would include both the before and after elements and list the columns that have changed.

For example, to add a new product to the Northwind Traders' Products table we could create the following HTML file called InsertProd.htm:

 <HTML> <SCRIPT> function InsertXML(ProdName, UInStock) { myTemplate = "http://localhost/northwind?template=" + "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" + "<sql:sync>" + "<sql:after>" + "<Products ProductName=\"" + ProdName + " UnitsInStock=\"" + UInStock + " \"/>" + "</sql:after>" + "</sql:sync>" + "<sql:query>select * from Products FOR XML AUTO " + "</sql:query>" + "</ROOT>"; alert(myTemplate); document.location.href = myTemplate; } </SCRIPT> <BODY> Product Name:<INPUT type="text" id="ProductName" value=""> <br></br> Units In Stock:<INPUT type="text" id="UnitInStock" value=""> <br></br> <INPUT type="button" value="insert" OnClick="InsertXML(ProductName.value, UnitInStock.value);"/> </BODY> </HTML> 

You can also find this HTML file on the companion CD. This HTML page uses a Java script function that builds the template. The backslash (/) is required for including a quote within a quote. You must place quotes around the new values or you will get an error. Unfortunately, there are some fields, such as money, which will not be accepted by SQL Server this way. These fields would need to be updated using a stored procedure as described below. Notice that we also included a sql:query element that is used to determine what is returned to the client. This will allow us to see whether the data was actually added to the database. Figure 15-6 shows what the HTML page and the query string look like.

Figure 15-6. The HTML page for updating with query string.

There are other more advanced features of the SQL ISAPI extension that extend beyond the level of this book. If you are interested in these features, you can look at the documentation that comes with the SQL ISAPI extension.