Creating Templates

As I mentioned, a template is an XML file containing a document based on the Microsoft XML-SQL namespace. The template usually contains at least one query. Let’s assume that Northwind Traders wants to publish its catalog on the Web. A sensible approach might be to allow customers to browse the catalog by product category. The first stage in building this solution would be to create a template that retrieves a list of categories, as shown in the following sample code:

 <?xml version=‘1.0’ ?> <categorylist xmlns:sql=‘urn:schemas-microsoft-com:xml-sql’>     <sql:query>         SELECT categoryid, categoryname         FROM categories         FOR XML AUTO, ELEMENTS     </sql:query> </categorylist> 

You can save this category list template as categories.xml in the onlinesales virtual name. From this code, you can see that the templates used with HTTP have the same format as the query templates used with ADO. When the template is accessed, the query tags are resolved into the XML fragments they return and the template containing the resolved query results is returned to the caller.

Creating Parameterized Templates

To make your templates more flexible, you can add parameters. This strategy allows users to request data based on one or more variable values. For example, a template containing a parameter would allow users to supply a category ID and return a list of the products in the specified category.

Parameters are placed in the header of the template, which is defined using the header tag. Each parameter is then defined using the param tag, with an optional default value. You could use the following template to retrieve products by category:

 <?xml version=‘1.0’ ?> <productlist xmlns:sql=‘urn:schemas-microsoft-com:xml-sql’>     <sql:header>         <sql:param name=‘categoryid’>1</sql:param>     </sql:header>     <sql:query>         SELECT productid, productname, unitprice         FROM products         WHERE categoryid = @categoryid         FOR XML AUTO, ELEMENTS     </sql:query> </productlist> 

This template is saved as Products.xml in the Demos\Chapter5\Templates folder on the companion CD. This template includes a single parameter with a default value of 1. Notice that the param tag includes a name attribute used to identify the parameter; you use this name in the SQL query by prefixing it with the @ symbol.

To access data using a parameterized template, you specify the parameters in the URL as a query string. For example, the products in category number 2 could be retrieved from the products.xml template using the following URL:

 http://webserver1/northwinddata/onlinesales/products.xml?categoryid=2 

You can execute this query by opening the shortcut named Product List in the Demos\Chapter5 folder on the companion CD.