Using URL Queries to Test a Virtual Directory

The easiest way to test that a virtual directory has been correctly configured is to enable URL queries and test the application by submitting queries using an XML-aware browser such as Internet Explorer. To ensure that your security configuration is working correctly, you should test the application using a remote client machine.

When you retrieve data from a URL, you use parameters to specify the query to be executed and other formatting settings. These parameters are passed using the standard format for URL query strings. To separate the parameters from the URL, use a question mark character (?). When you must pass a number of parameters, use an ampersand character (&) to separate them. For example, the following URL format would be used to access data from a URL using two parameters:

 http://webserver1/northwinddata?param1=value&param2=value 

Retrieving XML Documents Using a URL Query

An XML document can be retrieved using a URL query by specifying a sql parameter containing a FOR XML query. Because FOR XML queries return XML fragments rather than well-formed XML documents, a root element must be specified in the URL. You accomplish this by selecting the root element explicitly or by specifying a root parameter.

The following example shows a URL query that could be used to retrieve data from the Products table in the Northwind database. The root element is included as part of the sql parameter.

 http://webserver1/northwinddata?sql=SELECT+'<catalog>';SELECT+*+FROM     +products+FOR+XML+AUTO;SELECT+'</catalog>' 

You can execute this query by opening the shortcut named Explicit Root in the Demos\Chapter4 folder on the companion CD. This query could also be performed by specifying a root parameter, as shown in this example:

 http://webserver1/northwinddata?sql=SELECT+*+FROM+products      +FOR+XML+AUTO&root=catalog 

Encoding Special Characters in a URL Query

Some characters that could appear in a Transact-SQL query have a special meaning in a URL and must be encoded. For example, consider the following query:

 SELECT * FROM products WHERE productname LIKE 'G%' FOR XML AUTO 

Although this is a perfectly legal Transact-SQL query, it would return an error if executed through a URL because the percentage symbol, used in Transact-SQL as a wildcard, has a special meaning in a URL, where it is used to specify an encoded character. To execute the query in a URL, the percentage symbol should be encoded in hexadecimal as %25. A URL query containing the encoded symbol appears here:

 http://webserver1/northwinddata?sql=SELECT+*+FROM+products      +WHERE+productname+LIKE+'G%25'+FOR+XML+AUTO&root=catalog 

You can execute this query by opening the shortcut named Encode Characters in the Demos\Chapter4 folder on the companion CD. The following table shows the characters that must be encoded when you use them in a URL query:

Character	Meaning	Hexadecimal Encoding
+	Space	%20
/	Directory separator	%2F
?	Parameter marker	%3F
%	Encoded character marker	%25
#	Bookmark indicator	%23
&	Parameter separator	%26

Spaces aren't allowed in URLs and should be encoded using a plus (+) symbol or its hexadecimal equivalent (%20). For example, a URL might contain the parameter sql=SELECT+*+FROM+products+FOR+ XML. Internet Explorer automatically replaces spaces with hexadecimal-encoded plus symbols.

Specifying a Style Sheet in a URL Query

Say that you want to specify that an XSL style sheet should be applied to the XML data returned by a URL query. You can accomplish this task by storing the style sheet in the virtual directory referenced by the SQLISAPI application and adding an xsl parameter to the URL. The SQLOLEDB provider applies the style sheet to the XML data, and the IIS server returns the resulting document to the browser. You can use this technique to retrieve the data as HTML using a non-XML–aware browser.

To see how this technique could be useful, let's assume you want to retrieve the ProductID and ProductName fields from each record in the Products table and display them in an HTML table. You could create the following style sheet and save it as catalog.xsl in the SQL Server virtual directory:

 <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">     <xsl:template match="/">         <HTML>             <BODY>                      <TABLE border="1">                     <TR>                         <TD><B>Product ID</B></TD>                         <TD><B>Product Name</B></TD>                     </TR>                     <xsl:for-each select="catalog/products">                         <TR>                             <TD>                             <xsl:value-of select="@productid"/>                             </TD>                             <TD>                             <xsl:value-of select="@productname"/>                             </TD>                         </TR>                     </xsl:for-each>                 </TABLE>             </BODY>         </HTML>     </xsl:template> </xsl:stylesheet> 

This style sheet is also available in the Demos\Chapter4 folder on the companion CD. You could then specify the style sheet using the xsl parameter in a URL query, as shown here:

 http://webserver1/northwinddata?sql=SELECT+productid,+productname      +FROM+products+FOR+XML+AUTO&root=catalog&xsl=catalog.xsl 

You can execute this query by opening the shortcut named Apply Style Sheet in the Demos\Chapter4 folder on the companion CD. The data would be returned to the browser as HTML and rendered in a table, similar to the one shown here (which has been truncated for clarity):

Product ID	Product Name
17	Alice Mutton
3	Aniseed Syrup
40	Boston Crab Meat
60	Camembert Pierrot
18	Carnarvon Tigers

Specifying a Content Type

The SQLISAPI application returns the data with an appropriate content type specified in the header so that the client application or browser can properly render the results. For most queries, the data is returned with text/xml as its default content type, unless a style sheet that formats the data as HTML is used, in which case the content type defaults to text/html. You can override the default content type by adding a contenttype parameter to the URL, as shown in the following example:

 http://webserver1/northwinddata?sql=SELECT+productid,+productname      +FROM products+FOR+XML+AUTO&root=catalog&xsl=catalog.xsl     +&contenttype=text/xml 

In this example, the HTML produced by the style sheet is displayed by an XML-aware browser as an XML document rather than rendered, as shown here:

 <HTML>     <BODY>         <TABLE border="1">             <TR>                 <TD><B>Product ID</B></TD>                 <TD><B>Product Name</B></TD>             </TR>             <TR>                 <TD>17</TD>                 <TD>Alice Mutton</TD>             </TR>             <TR>                 <TD>3</TD>                 <TD>Aniseed Syrup</TD>             </TR>                              </TABLE>     </BODY> </HTML> 

You can execute this query by opening the shortcut named Content Type in the Demos\Chapter4 folder on the companion CD.

Returning Non-XML Query Results

The ability to specify the content type is particularly useful when you're using an XML-aware browser, such as Internet Explorer, to retrieve data that isn't a fully well-formed XML document. If you execute a Transact-SQL query with no FOR XML clause, your data is simply returned as a character stream; if you execute a FOR XML query with no root element specified, your data will come back as one or more XML fragments. Returning either of these sets of data to an XML-aware browser without specifying a content type other than XML would result in a parsing error.

You can use HTTP to execute queries with no FOR XML clause to return a single column from a table or a view, as shown in the following example:

 http://webserver1/northwinddata?sql=SELECT+productname      +FROM+products&contenttype=text/html 

You can execute this query by opening the shortcut named Non XML Query in the Demos\Chapter4 folder on the companion CD. This code returns a stream containing the Productname field for each row, as shown here:

 Alice MuttonAniseed SyrupBoston Crab MeatCamembert PierrotCarnarvon     TigersChaiChang ... 

Because the data is returned in a nondelimited stream, this approach is probably most useful for fixed-width columns.

XML fragments can be returned with no root element, allowing you to use custom client logic to build a well-formed XML document. For example, you could execute the following URL query:

 http://webserver1/northwinddata?sql=SELECT+productid,+productname      +FROM+products+FOR+XML+AUTO&contenttype=text/html 

Data returned to an XML-aware browser using this approach results in a blank Web page because browsers don't render the contents of a tag. If you look at the source of the page, you can see the actual XML fragments returned to the browser, as shown here:

 <products product productname="Alice Mutton"/> <products product productname="Aniseed Syrup"/> <products product productname="Boston Crab Meat"/> <products product productname="Camembert Pierrot"/> <products product productname="Carnarvon Tigers"/>  

If you also specify the ELEMENTS option in the URL query, the element values in the element-centric XML fragments will be rendered as a character stream and returned to the browser.

Executing Stored Procedures in a URL Query

You can execute a stored procedure using either the Transact-SQL EXECUTE syntax or the Open Database Connectivity (ODBC) CALL syntax. In either case, you can specify parameters by position or by name with the value. For example, you can create a stored procedure in the Northwind database using the following Transact-SQL script:

 CREATE PROC getpricelist @category integer AS SELECT productname, unitprice FROM products WHERE categoryid = @category FOR XML AUTO 

To call this stored procedure in a URL query, you can use the EXECUTE syntax and specify the parameter by position, as shown here:

 http://webserver1/northwinddata?sql=EXECUTE+getpricelist+2     +&root=pricelist 

You can execute this query by opening the shortcut named Execute Proc in the Demos\Chapter4 folder on the companion CD. You could also specify the parameter by name with the value, as shown here:

 http://webserver1/northwinddata?sql=EXECUTE+getpricelist      +@category=2&root=pricelist 

Another option is to use ODBC CALL syntax to execute the stored procedure:

 http://webserver1/northwinddata?sql={CALL+getpricelist}+2     +&root=pricelist 

Again, the parameter could be passed by name with the value:

 http://webserver1/northwinddata?sql={CALL+getpricelist}     +@category=2&root=pricelist 

You can execute this query by opening the shortcut named Call Proc in the Demos\Chapter4 folder on the companion CD. Because one syntax isn't really better than another, most developers use the format most familiar to them.