Executing SQL via HTTP

Up to this point, we've given a lot of examples to show how the HTTP protocol is used to request XML documents from SQL Server. We've never really given a formal definition of the syntax. Table 4.3 gives the formal definition. We'll also cover some more details of querying via a URL, such as stored procedures, templates in depth, utilizing XSLT stylesheets, and so on.

Here's the formal syntax for URL access accepted by the SQL ISAPI extension:

  http:  //iisserver/virtualroot/virtualname[/pathinfo][/XPathExpression]  [  ?param  =value[  &param  =value]...n] 

or:

 http://iisserver/virtualroot?{sql=SqlString  template=XMLTemplate}  [&param=value[&param=value]...n] 

Table 4.3. HTTP Syntax Explanation

Keyword

Description

iisserver

The IIS server to access.

For example: www.newriders.com .

virtualroot

The virtual root configured with theVirtual Directory Management utility (graphically or programmatically).

virtualname

A virtual name defined when the virtual root was configured. It will be one of three types: template, schema, or dbobject.

[/pathinfo]

Path information to locate template or schema files in addition to the path information specified during virtualname configuration. Not needed for a dbobject type.

[/XPathExpression]

Specified if necessary for schema files or dbobject types.

?sql

Delimits an SQL query string.

SqlString

An SQL query or stored procedure name. Usually contains the FOR XML extension unless the returned data is already in XML format. For example, a stored procedure that returns XML data.

?template

Delimits a SQL query string formatted as an XML document.

param

This is either a parameter name or one of the following:

contenttype	Specifies the content format of the returned document to allow a Web browser to pick the proper display method. Specified in two parts , the contenttype and subtype . It's sent in the HTTP header to become the MIME type of the document. text/XML, text/HTML, and image/gif designate an XML document, an HTML document, and a GIF image, respectively.
outputencoding	The character set used to render the generated XML document. The default is UTF8.Templates specified directly in a URL via template= are rendered in Unicode. Template files can specify the outputencoding themselves because they are XML documents.
root	When specified, the returned data is bracketed with the element name given to generate a well- formed XML document.
xsl	The URL of an XSLT stylesheet used to process the returned data. By default, output documents are UTF-8 encoded unless overridden by an encoding instruction in the XSL file. If outputencoding is specified, it overrides the XSL specified encoding.

Character encodings are specified with the encoding= attribute in the XML declaration. The XML specification explicitly says XML uses ISO 10646, the international standard 31-bit character repertoire that covers most human languages. It is planned to be a superset of Unicode and can be found at http://www.iso.ch.

The spec says (2.2) "All XML processors must accept the UTF-8 and UTF-16 encodings of ISO 10646 ." UTF-8 is an encoding of Unicode into 8-bit characters : The first 128 are the same as ASCII; the rest are used to encode the rest of Unicode into sequences of between 2 and 6 bytes. UTF-8 in its single-octet form is therefore the same as ISO 646 IRV (ASCII), so you can continue to use ASCII for English or other unaccented languages using the Latin alphabet. Note that UTF-8 is incompatible with ISO 8859-1 (ISO Latin-1) after code point 126 decimal (the end of ASCII). UTF-16 is like UTF-8 but with a scheme to represent the next 16 planes of 64k characters as two 16-bit characters.

Regardless of the encoding used, any character in the ISO 10646 character set can be referred to by the decimal or hexadecimal equivalent of its bit string. So, no matter which character set you personally use, you can still refer to specific individual characters by using &#dddd; (decimal character code) or &#xHHHH; (hexadecimal character code in uppercase). The terminology can get confusing, as can the numbers : See the ISO 10646 Concept Dictionary at http://www.cns-web.bu.edu/djohnson/web_files/i18n/ISO-10646.html.

Well-Formed Documents, Fragments, and &root

As promised , the &root=root parameter used in some of the URLs and template files in the previous section requires a little explanation.

Think back to our discussion of well- formedness and XML documents. One of the conditions that must be met for an XML document to be well-formed is that it must contain a single top-level element. Remember our RESUMES shown in the following example:

 <RESUMES xmlns='http://www.myorg.net/tags'>    <PERSON PERSONID="p1">      <NAME>  ...  </RESUMES> 

Our document contains the single top-level element <RESUMES> . Although there are other requirements for well-formed documents, if this one isn't met, the document fails the test.

How does this relate to the &root parameter in the URL? The &root parameter specifies the name of the document ROOT element. The result is an XML document with that all-important single top-level element. Let's look at some examples by reusing some of the code we have given previously:

 http://IISServer/Nwind?sql=SELECT+*+FROM+Employees+FOR+XML+AUTO&root=root 

Here we specify &root=root . This will generate a document that contains that single top-level element, and the element will be <ROOT> . In the following example, you would expect a document fragment to be returned because no root element is specified. You actually receive an error message stating , "Only one top-level element is allowed in an XML document." Because the root element is missing, all employee elements are assumed to be at the top level, which isn't allowed.

 http://IISServer/Nwind?sql=SELECT+*+FROM+Employees+FOR+XML+AUTO 

When template files are used, the same conditions hold true. Let's say we have the template file shown in Listing 4.4.

Listing 4.4 Template File Without a Root Element

 <ROOT XMLNS:SQL="urn:schemas-microsoft-com:xml-sql">    <sql:query>      SELECT  *      FROM    Employees      FOR XML AUTO    </sql:query>  </ROOT> 

Let's say we employ this template file via this URL:

 http://IISServer/Nwind/TemplateVirtualName/template.xml 

The template file will provide the single top-level element via the <ROOT> element declaration.

Just to make sure we understand the root declaration, if I make the specification root=EMPS in the template file, the following fragment shows how the resulting document's root element has changed.

 <EMPS xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <Employees EmployeeID="1: LastName="Davolio" ...  ...  </EMPS> 

Queries on Multiple Tables

Querying multiple tables has implications that you need to consider carefully if you want to generate your resulting documents with the proper element order. Here's the rule of thumb: "The order in which tables are specified in the SQL query determines the element nesting order." We'll take a look at the Orders and Employees tables in Northwind in a couple of ways (also refer to Appendix A, "Northwind Database Schema").

Here's the first query:

 http://iisserver/Nwind?sql=SELECT+TOP+2+Orders.OrderID,+Employees.  LastName,+Orders.ShippedDate+FROM+Orders,+Employees+WHERE+Orders.  EmployeeID=Employees.EmployeeID+Order+by+Employees.EmployeeID,  OrderID+FOR+XML+AUTO&  root=ROOT 

This returns the results in Listing 4.5.

Listing 4.5 Results of Querying Multiple Tables

 <?xml version="1.0" encoding="utf-8" ?>  <ROOT>    <Orders OrderID="10258" ShippedDate="1996-07-23T00:00:00">      <Employees LastName="Davolio" />    </Orders>    <Orders OrderID="10270" ShippedDate="1996-08-02T00:00:00">      <Employees LastName="Davolio" />    </Orders>  </ROOT> 

Let's do this again with three tables, this time adding the Order Details table.

 http://iisserver/Nwind?sql=SELECT+TOP+2+Orders.OrderID,+Employees.  LastName,+Orders.ShippedDate,+[Order+Details].UnitPrice,+[Order+  Details].ProductID+FROM+Orders,+Employees,+[Order+Details]+WHERE+Orders.  EmployeeID=Employees.EmployeeID+AND+Orders.OrderID=[Order+Details].OrderID  +Order+by+Employees.EmployeeID,Orders.OrderID+FOR+XML+AUTO&root=ROOT 

Listing 4.6 shows the results of this query.

Listing 4.6 Results of Querying Three Tables

 <?xml version="1.0" encoding="utf-8" ?>  <ROOT>    <Orders OrderID="10258" ShippedDate="1996-07-23T00:00:00">      <Employees LastName="Davolio">        <Order_x0020_Details UnitPrice="15.2" ProductID="2" />        <Order_x0020_Details UnitPrice="17" ProductID="5" />      </Employees>    </Orders>  </ROOT> 

Listing 4.7 shows the results obtained when we execute the same SQL expression but move the Employees.LastName element to the last element specified.

Listing 4.7 Results of Moving Employees.LastName to the Last Element

 <?xml version="1.0" encoding="utf-8" ?>  <ROOT>    <Orders OrderID="10258" ShippedDate="1996-07-23T00:00:00">      <Order_x0020_Details UnitPrice="15.2" ProductID="2">        <Employees LastName="Davolio" />      </Order_x0020_Details>      <Order_x0020_Details UnitPrice="17" ProductID="5">        <Employees LastName="Davolio" />      </Order_x0020_Details>    </Orders>  </ROOT> 

The key point I want you to grasp here is that the placement of elements in the result XML document depends on their placement in the SQL expression. This should be especially evident in the difference between Listings 4.6 and 4.7.

Passing Parameters

It is possible to pass parameters to SQL queries in URLs. This is known as run-time substitution as opposed to design-time substitution. In this case, we use a placeholder to specify the location where the parameter is to be substituted at execution time. The placeholder is a ? , which must be specified as %3F in a URL. Here's an example:

 http://iisserver/Nwind?sql=SELECT+TOP+4+OrderID+FROM+Orders+WHERE+  EmployeeID=%3F +FOR+XML+AUTO&EmployeeID=5&root=ROOT 

Listing 4.8 is the resulting document.

Listing 4.8 Results of Parameter Substitution in Our URL

 <?xml version="1.0" encoding="utf-8" ?>  <ROOT>    <Orders OrderID="10248" />    <Orders OrderID="10254" />    <Orders OrderID="10269' />    <Orders OrderID="10297" />  </ROOT> 

Passing multiple parameters would just consist of more than one item with a question mark and a separate parameter for each. This URL passes two parameters:

 http://iisserver/Nwind?sql=SELECT+TOP+4+OrderID+FROM+Orders+WHERE+  EmployeeID=%3F+AND+CustomerID=%3F+FOR+XML+AUTO&EmployeeID=5&CustomerID=  "VINET"&root=ROOT 

This might not seem like a big deal here, and perhaps it's not to you, but wait until we start specifying template files in URLs that have parameters queries designed into them. We'll get to these shortly.

The XSL Keyword

Now we get to make use of what we learned in Chapter 2. Utilizing XSLT stylesheets gives us the much-needed flexibility to manipulate the XML output we generate. We can create HTML on-the-fly for immediate or later display, or we can change the returned XML document to a different one for further processing. The latter occurs more often than you would think, as with Electronic Data Interchange (EDI) related messages. Let's take the following SQL query and generate an HTML page:

 http://iisserver/Nwind?sql=SELECT+TOP+4+OrderID,EmployeeID,Shipname+FROM+  Orders+WHERE+EmployeeID=5+FOR+XML+AUTO&xsl=order.xsl &root=ROOT 

In this example, the XSL file is located in the virtual root directory.The resulting XML document is given in Listing 4.9, followed by the XSLT stylesheet in Listing 4.10.

Listing 4.9 XML Document Containing Order Information

 <?xml version="1.0" encoding="utf-8" ?>  <ROOT>    <Orders OrderID="10248" EmployeeID="5" Shipname="Vins et alcools     Chevalier" />    <Orders OrderID="10254" EmployeeID="5" Shipname="Chop-suey Chinese" />    <Orders OrderID="10269" EmployeeID="5" Shipname="White Clover Markets"  />    <Orders OrderID="10297" EmployeeID="5" Shipname="Blondel pre et fils"  />  </ROOT> 

Listing 4.10 XSLT Stylesheet to Apply to Order Information

 <?xml version="1.0"?>  <xsl:stylesheet xmlns:xsl="http://www.w3.org/XSL/Transform/1.0">  <xsl:output media-type="text/html"/>    <xsl:template match="/">      <HTML>      <BODY>        <TABLE width="400" border="1">        <TR>        <TD><B>Order ID</B></TD>        <TD><B>Ship Name</B></TD>        </TR>        <xsl:apply-templates/>        </TABLE>      </BODY>      </HTML>    </xsl:template>    <xsl:template match="Orders">      <TR>        <TD>          <xsl:value-of select="@OrderID"/>        </TD>        <TD>          <xsl:value-of select="@Shipname"/>        </TD>      </TR>    </xsl:template>  </xsl:stylesheet> 

This results in a simple table of Order ID versus Shipname, as shown in Table 4.4.

Table 4.4. HTML Table of Results

Order ID	Shipname
10248	Vins et alcools Chevalier
10254	Chop-suey Chinese
10269	White Clover Markets
10297	Blondel per`e et fils

Again, the result in this case might be simple, but the potential is enormous . Data can be retrieved from a database and presented in real-time to the viewer via a thin-client browser. Static pages can be generated and stored for viewing as needed by the client. In this case, the XSLT stylesheet functions as an HTML template. (Templates are much easier to maintain than rewriting HTML documents.) Business-to-business e-commerce documents can be generated from existing queries without having to modify the query itself. The XSLT stylesheet can manipulate the data in any way desired to create the required new XML document.

Next we change our focus to using template files to generate XML documents. We'll discuss them in a lot more detail than we have so far.