Template Queries

for RuBoard

A safer and more widely used technique for retrieving data over HTTP is to use server-side XML templates that encapsulate Transact-SQL queries. Because these templates are stored on the Web server and are referenced via a virtual name , the end user never sees their source code. They are XML documents based on the XML-SQL namespace and function as a mechanism for translating a URL into a query that SQL Server can process. As with plain URL queries, results from template queries are returned as either XML or HTML.

Here's a simple XML query template:

 <?xml version='1.0' ?>  <CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'>  <sql:query>         SELECT CustomerId, CompanyName         FROM Customers         FOR XML AUTO     </sql:query> </CustomerList> 

Note the use of the sql namespace prefix with the query itself. This is made possible by the namespace reference on the second line of the template (in bold type).

Here we're merely returning two columns from the Northwind Customers table, as we've been doing for most of the chapter. We include FOR XML AUTO to return the data as XML. Here's a URL that uses the template, along with the data it returns:

http://localhost/Northwind/templates/CustomerList.XML

(Results abridged)

 <?xml version="1.0" ?> _ <CustomerList xmlns:sql="urn:schemas-microsoft-com:xml-sql">        <Customers CustomerId="ALFKI" CompanyName="Alfreds Futterkiste" />        <Customers CustomerId="VAFFE" CompanyName="Vaffeljernet" />        <Customers CustomerId="VICTE" CompanyName="Victuailles en stock" />        <Customers CustomerId="VINET" CompanyName="Vins et alcools Chevalier" />        <Customers CustomerId="WARTH" CompanyName="Wartian Herkku" />        <Customers CustomerId="WELLI" CompanyName="Wellington Importadora" />        <Customers CustomerId="WHITC" CompanyName="White Clover Markets" />        <Customers CustomerId="WILMK" CompanyName="Wilman Kala" />        <Customers CustomerId="WOLZA" CompanyName="Wolski Zajazd" /> </CustomerList> 

Notice that we're using the templates virtual name that we created under the Northwind virtual directory earlier.

Parameterized Templates

You can also create parameterized XML query templates that permit the user to supply parameters to the query when it's executed. You define parameters in the header of the template, which is contained in its sql:header element. Each parameter is defined using the sql:param tag and can include an optional default value. Here's an example:

 <?xml version='1.0' ?> <CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'>     <sql:header>  <sql:param name='CustomerId'>%</sql:param>  </sql:header>     <sql:query>         SELECT CustomerId, CompanyName         FROM Customers         WHERE CustomerId LIKE @CustomerId         FOR XML AUTO     </sql:query> </CustomerList> 

Note the use of sql:param to define the parameter. Here we give the parameter a default value of "%" because we're using it in a LIKE predicate in the query. This means that we list all customers if no value is specified for the parameter.

Note that SQLISAPI is smart enough to submit a template query to the server as an RPC when you define query parameters. It binds the parameters you specify in the template as RPC parameters and sends the query to SQL Server using RPC API calls. This is more efficient than using T-SQL language events and should result in better performance, particularly on systems with high throughput.

Here's an example of URL that specifies a parameterized template query, along with its results:

http://localhost/Northwind/Templates/CustomerList2.XML?CustomerId=A%25

(Results)

 <?xml version="1.0" ?> - <CustomerList xmlns:sql="urn:schemas-microsoft-com:xml-sql">        <Customers CustomerId="ALFKI" CompanyName="Alfreds Futterkiste" />        <Customers CustomerId="ANATR" CompanyName="Ana Trujillo Emparedados y helados" />        <Customers CustomerId="ANTON" CompanyName="Antonio Moreno Taquera" />        <Customers CustomerId="AROUT" CompanyName="Around the Horn" /> </CustomerList> 

Style Sheets

As with regular URL queries, you can specify a style sheet to apply to a template query. You can do this in the template itself or in the URL that accesses it. Here's an example of a URL that applies a style sheet to a template query:

http://localhost/Northwind/Templates/CustomerList3.XML?xsl=Templates/CustomerList3.xsl& contenttype =text/html

Note the use of the contenttype parameter to force the output to be treated as HTML (in bold type). We do this because we know that the style sheet we're applying translates the XML returned by SQL Server into an HTML table.

We include the relative path from the virtual directory to the style sheet because it's not automatically located in the Templates folder even though the XML document is located there. The path specifications for a template query and its parameters are separate from one another.

As I've mentioned, the XML-SQL namespace also supports specifying the style sheet in the template itself. Here's a template that specifies a style sheet:

 <?xml version='1.0' ?> <CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql' sql:xsl='CustomerList3.xsl'>     <sql:query>         SELECT CustomerId, CompanyName         FROM Customers         FOR XML AUTO     </sql:query> </CustomerList> 

Here's the style sheet the template references:

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

Here's a URL that uses the two of them, along with the results it produces:

http://localhost/Northwind/Templates/CustomerList4.XML?contenttype=text/html

(Results abridged)

Note that, once again, we specify the contenttype parameter to force the output to be treated as HTML. This is necessary because XML-aware browsers such as Internet Explorer automatically treat the output returned by XML templates as text/xml. Because the HTML we're returning is also well- formed XML, the browser doesn't know to render it as HTML unless we tell it to. That's what the contenttype specification is for: It causes the browser to render the output of the template query as it would any other HTML document.

TIP

While developing XML templates and similar documents that you then test in a Web browser, you may run into problems with the browser caching old versions of documents, even when you click the Refresh button or hit the Refresh key (F5). In Internet Explorer, you can press Ctrl-F5 to cause a document to be completely reloaded, even if the browser doesn't think it needs to be. Usually, this resolves problems with an old version persisting in memory after you've changed the one on disk.

Applying Style Sheets on the Client

If the client is XML enabled, you can also apply style sheets to template queries on the client side. This off-loads a bit of the work of the server, but requires a separate round-trip to download the style sheet to the client. If the client is not XML enabled, the style sheet will be ignored, making this approach more suitable to situations in which you know for certain whether your clients are XML enabled, such as with private intranet or corporate applications.

Here's a template that specifies a client-side style sheet translation:

 <?xml version='1.0' ?>  <?xml-stylesheet type='text/xsl' href='CustomerList3   .   xsl'?>  <CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'>     <sql:query>         SELECT CustomerId, CompanyName         FROM Customers         FOR XML AUTO     </sql:query> </CustomerList> 

Note the xml-stylesheet specification at the top of the document (in bold type). This tells the client-side XML processor to download the style sheet specified in the href attribute and apply it to the XML document rendered by the template. Here's the URL and results:

http://localhost/Northwind/Templates/CustomerList5.XML?contenttype=text/html

(Results abridged)

Client-side Templates

As I mentioned earlier, it's far more popular (and safer) to store templates on your Web server and route users to them via virtual names. That said, there are times when allowing the user the flexibility to specify templates on the client side is very useful. Specifying client-side templates in HTML or in an application alleviates the necessity to set up the templates in advance or the virtual names that reference them. Although this is certainly easier from an administration standpoint, it's potentially unsafe on the public Internet because it allows clients to specify the code they run against your SQL Server. Use of this technique should probably be limited to private intranets and corporate networks.

Here's a Web page that embeds a client-side template:

 <HTML>     <HEAD>         <TITLE>Customer List</TITLE>     </HEAD>     <BODY>         <FORM action='http://localhost/Northwind' method='POST'>             <B>Customer ID Number</B>             <INPUT type=text name=CustomerId value='%'>            <INPUT type=hidden name=xsl value=Templates/CustomerList2.xsl>             <INPUT type=hidden name=template value='  <CustomerList xmlns:sql="urn:schemas-microsoft-com:xml-sql">   <sql:header>   <sql:param name="CustomerId">%</sql:param>   </sql:header>   <sql:query>   SELECT CompanyName, ContactName   FROM Customers   WHERE CustomerId LIKE @CustomerId   FOR XML AUTO   </sql:query>   </CustomerList>   '>  <P><input type='submit'>         </FORM>     </BODY> </HTML> 

The client-side template (in bold type) is embedded as a hidden field in the Web page. If you open this page in a Web browser, you should see an entry box for a Customer ID and a Submit button. Entering a customer ID or mask and clicking Submit Query will post the template to the Web server. SQLISAPI will then extract the query contained in the template and run it against SQL Server's Northwind database (because of the template's virtual directory reference). The CustomerList2.xsl style sheet will then be applied to translate the XML document that SQL Server returns into HTML, and the result will be returned to the client. Here's an example:

(Results)

As with server-side templates, client-side templates that include parameters are sent to SQL Server using an RPC.

for RuBoard