XML —

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.