URL Queries

for RuBoard

The easiest way to test your new virtual directory is to submit a URL query that uses it from an XML-enabled browser such as Internet Explorer. URL queries take this form:

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO&root=Customers

NOTE

As with all URLs, the URL listed here should be typed on one line. Page width restrictions may force some of the URLs listed in this book to span multiple lines, but a URL should always be typed on a single line.

Here, localhost is the name of the Web server. It could just as easily be a fully qualified DNS domain name such as www.nkandescent.com. Northwind is the virtual directory name we created earlier.

A question mark separates the URL from its parameters. Multiple parameters are separated by ampersands. The first parameter we pass here is named sql. It specifies the query to run. The second parameter specifies the name of the root element for the XML document that will be returned. By definition, you get just one of these per document. Failure to specify a root element results in an error if your query returns more than one top-level element.

To see how this works, submit the following URL from your Web browser (be sure to change localhost to the correct name of your Web server if it resides on a different machine):

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE+CustomerId='ALFKI'+FOR+XML+AUTO

(Results)

 <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545" /> 

Notice that we left off the root element specification. Look at what happens when we bring back more than one row:

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE+CustomerId='ALFKI'+OR+CustomerId='ANATR'+FOR+XML+AUTO

(Results abridged)

 The XML page cannot be displayed Only one top level element is allowed in an XML document. Line 1, Position 243 

Because we're returning multiple top-level elements (two, to be exact), our XML document has two root elements named Customers, which, of course, isn't allowed because it isn't well-formed XML. To remedy the situation, we need to specify a root element. This element can be named anything. It serves only to wrap the rows returned by FOR XML so that we have a well- formed document. Here's an example:

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE+CustomerId='ALFKI'+OR+CustomerId='ANATR'+FOR+XML+AUTO&root=CustomerList

(Results)

 <?xml version="1.0" encoding="utf-8" ?> _ <CustomerList>    <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"       ContactName="Maria Anders" ContactTitle="Sales Representative"       Address="Obere Str. 57" City="Berlin" PostalCode="12209"       Country="Germany" Phone="030-0074321" Fax="030-0076545" />    <Customers CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y       helados" ContactName="Ana Trujillo" ContactTitle="Owner" Address="Avda.       de la Constituci?n 2222" City="M?xico D.F." PostalCode="05021"       Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745" /> </CustomerList> 

You can also supply the root element yourself as part of the sql parameter, like this:

 http://localhost/Northwind?sql=SELECT+'<CustomerList>'; SELECT+*+FROM+Customers+WHERE+CustomerId='ALFKI'+OR+CustomerId='ANATR'+FOR+XML+AUTO;SELECT+'</CustomerList>'; 

(Results formatted)

 _ <CustomerList>     <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"        ContactName="Maria Anders" ContactTitle="Sales Representative"        Address="Obere Str. 57" City="Berlin" PostalCode="12209"        Country="Germany" Phone="030-0074321" Fax="030-0076545" />     <Customers CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y        helados" ContactName="Ana Trujillo" ContactTitle="Owner" Address="Avda.        de la Constituci?n 2222" City="M?xico D.F." PostalCode="05021"        Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745" /> </CustomerList> 

The sql parameter of this URL actually contains three queries. It uses the same technique as the UNION examples we built in the HTML chapter to generate three distinct pieces of data: It uses multiple queries. The first one generates an opening tag for the root element, the second is the query itself, and the third generates a closing tag for the root element. We separate the individual queries with semicolons.

As you can see, FOR XML returns XML document fragments , so you'll need to provide a root element to produce a well-formed document.

Special Characters

Certain characters that are perfectly valid in Transact-SQL can cause problems in URL queries because they have special meanings within a URL. You've already noticed that we're using "+" to signify a space character. Obviously, this precludes the direct use of "+" in the query itself. Instead, you must encode characters that have special meaning within a URL query so that SQLISAPI can properly translate them before passing on the query to SQL Server. Encoding a special character amounts to specifying a percent sign (%) followed by the character's ASCII value in hexadecimal. Table 13-2 lists the special characters recognized by SQLISAPI and their corresponding values:

Table 13-2. Characters that Have Special Meaning in a URL Query and Their Hexadecimal Values

Character	Hexadecimal value
+	2B
&	26
?	3F
%	25
/	2F
#	23

Here's a URL query that illustrates how to encode special characters:

http://localhost/Northwind?sql=SELECT+'<CustomerList>';SELECT+*+FROM+Customers+WHERE+CustomerId+LIKE+'A%25'+FOR+XML+AUTO;SELECT+'</CustomerList>';

This query specifies a LIKE predicate that includes an encoded percent sign, Transact-SQL's wildcard symbol. Hexadecimal 25 (decimal 37) is the ASCII value of the percent sign, so we encode it as %25.

Style Sheets

In addition to the sql and root parameters, a URL query can also include the xsl parameter to specify an XML style sheet to use to translate the XML document that's returned by the query into a different format. The most common use of this feature is to translate the document into HTML. This allows you to view the document using browsers that aren't XML aware, and gives you more control over the display of the document in those that are. Here's a URL query that includes the xsl parameter:

http://localhost/Northwind?sql=SELECT+CustomerId,+CompanyName+FROM+Customers+FOR+XML+AUTO&root=CustomerList&xsl=CustomerList.xsl

Here's the XSL style sheet it references and the output that's produced:

 <?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>Customer ID</B></TD>                         <TD><B>Company Name</B></TD>                     </TR>                     <xsl:for-each select="CustomerList/Customers">                         <TR>                             <TD>                             <xsl:value-of select="@CustomerId"/>                             </TD>                             <TD>                             <xsl:value-of select="@CompanyName"/>                             </TD>                         </TR>                     </xsl:for-each>                 </TABLE>             </BODY>         </HTML>     </xsl:template> </xsl:stylesheet> 

(Results abridged)

Content Type

By default, SQLISAPI returns the results of a URL query with the appropriate type specified in the header so that a browser can properly render it. When FOR XML is used in the query, this is text/xml unless the xsl attribute specifies a style sheet that translates the XML document into HTML. In that case, text/html is returned.

You can force the content type using the contenttype URL query parameter, like this:

http://localhost/Northwind?sql=SELECT+CustomerId,+CompanyName+FROM+Customers+FOR+XML+AUTO&root=CustomerList&xsl=CustomerList.xsl&contenttype=text/xml

Here we've specified the style sheet from the previous example to cause the content type to default to text/html. Then we override this default by specifying a contenttype parameter of text/xml. The result is an XML document containing the translated result set:

 - <HTML> -       <BODY>               - <TABLE border="1">                      - <TR>                            - <TD>                                      <B>Customer ID</B>                              </TD>                            - <TD>                                      <B>Company Name</B>                              </TD>                      </TR>                      - <TR>                              <TD>ALFKI</TD>                              <TD>Alfreds Futterkiste</TD>                      </TR>                      - <TR>                              <TD>ANATR</TD>                              <TD>Ana Trujillo Emparedados y helados</TD>                       </TR>                       <TR>                              <TD>WILMK</TD>                              <TD>Wilman Kala</TD>                      </TR>                      - <TR>                              <TD>WOLZA</TD>                              <TD>Wolski Zajazd</TD>                      </TR>                 </TABLE>          </BODY> </HTML> 

So, even though the document consists of well-formed HTML, it's rendered as an XML document because we've forced the content type.

Non-XML Results

Being able to specify the content type comes in particularly handy when working with XML fragments in an XML-aware browser. As I mentioned earlier, executing a FOR XML query with no root element results in an error. You can, however, work around this by forcing the content to HTML, like this:

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE+CustomerId='ALFKI'+OR+CustomerId='ANATR'+FOR+XML+AUTO&contenttype=text/html

If you load this URL in a browser, you'll probably see a blank page because most browsers ignore tags they don't understand. However, you can view the source of the Web page and you'll see the XML fragment returned as you'd expect. This would be handy in situations when you're communicating with SQLISAPI using HTTP from outside a browserfrom an application of some sort . You could return the XML fragment to the client, then use client-side logic to apply a root element and/or process the XML further.

SQLISAPI also allows you to omit the FOR XML clause to return a single column from a table, view, or table-valued function as a plain text stream, like this:

http://localhost/Northwind?sql=SELECT+CAST(CustomerId+AS+char(10))+AS+CustomerId+FROM+Customers+ORDER+BY+CustomerId&contenttype=text/html

(Results)

 ALFKI ANATR ANTON AROUT BERGS BLAUS BLONP BOLID BONAP BOTTM BSBEV CACTU CENTC CHOPS COMMI CONSH DRACD DUMON EASTC ERNSH FAMIA FISSA FOLIG FOLKO FRANK FRANR FRANS FURIB GALED GODOS GOURL GREAL GROSR HANAR HILAA HUNGC HUNGO ISLAT KOENE LACOR LAMAI LAUGB LAZYK LEHMS LETSS LILAS LINOD LONEP MAGAA MAISD MEREP MORGK NORTS OCEAN OLDWO OTTIK PARIS PERIC PICCO PRINI QUEDE QUEEN QUICK RANCH RATTC REGGC RICAR RICSU ROMEY SANTG SAVEA SEVES SIMOB SPECD SPLIR SUPRD THEBI THECR TOMSP TORTU TRADH TRAIH VAFFE VICTE VINET WANDK WARTH WELLI WHITC WILMK WOLZA 

Note that SQLISAPI doesn't support returning multicolumn results. That said, this is still a handy way to quickly return a simple data list.

Stored Procedures

You can execute stored procedures via URL queries just as you can other types of Transact-SQL queries. Of course, this procedure needs to return its result set as XML if you intend to process it as XML in the browser or client application. Here's a stored procedure that illustrates:

 CREATE PROC ListCustomersXML @CustomerId varchar(10)='%', @CompanyName varchar(80)='%' AS SELECT CustomerId, CompanyName FROM Customers WHERE CustomerId LIKE @CustomerId AND CompanyName LIKE @CompanyName FOR XML AUTO 

Once your procedure correctly returns results in XML format, you can call it from a URL query using the Transact-SQL EXEC command. Here's an example of a URL query that calls a stored procedure using EXEC :

http://localhost/Northwind?sql=EXEC+ListCustomersXML+@CustomerId='A%25',@CompanyName='An%25'&root=CustomerList

(Results)

 <?xml version="1.0" encoding="utf-8" ?> _ <CustomerList>          <Customers CustomerId="ANATR" CompanyName="Ana Trujillo Emparedados y helados" />          <Customers CustomerId="ANTON" CompanyName="Antonio Moreno Taquer?a" />   </CustomerList> 

Notice that we specify the Transact-SQL wildcard character "%" using its encoded equivalent, %25. This is necessary, as I said earlier, because % has special meaning in a URL query.

TIP

You can also use the ODBC CALL syntax to call a stored procedure from a URL query. Because you have no way of binding RPC parameters from a URL query, if you supply any parameters to the procedure, it's translated to a regular language event on the server. However, if the procedure takes no parameters, the procedure will execute as an RPC call on the server, which is generally faster and more efficient than normal T-SQL language events. On high-volume Web sites, the small difference in performance this makes can add up quickly.

Here's a URL query that uses the ODBC CALL syntax:

http://localhost/Northwind?sql={CALL+ListCustomersXML}+&root=CustomerList

If you submit this URL from your Web browser while you have a Profiler trace running that includes all RPC events, you should see an RPC:Starting event for the procedure. This indicates that the procedure is being called via the more efficient RPC mechanism rather than via a language event, as I mentioned in Chapter 1.

See the Template Queries section later in this chapter for information on making RPCs that support parameters from XML.

for RuBoard