Executing Template Files via HTTP | XML and SQL Server 2000

If you look back at some of the queries we wrote in the previous section, you'll see that they can be pretty difficult to read sometimes. Take a look at the query that generated Listings 4.6 and 4.7 and tell me that you immediately know exactly what the query is doing. I doubt you can.

Template files have the same functionality as SQL queries written directly in URLs.Template files can do the following:

Specify SQL queries or XPath queries
Define parameters that can be passed to these queries
Specify a top-level (root) element for the XML document
Declare namespaces
Specify an XSLT stylesheet to apply to the results

Template files have the added benefits of being easier to read and some say easier to write. In addition, they remove the database details from the general user for added security. Editing a file can be made impossible for the user, but because he can see a URL, he can change it or write his own and obtain information you might not want him to see or have. Also, there are fewer training requirements because the user only needs to know the filename and any parameters that might need to be passed.

Using XML Templates

Up to this point, when we've written a template file, we've used only the <sql:query> element to specify what the statement is to execute. In addition to this <sql:query> element, there are four other elements that can appear in a template file. Listing 4.11 shows the general format of a template file and is followed by an explanation of each of the elements in Table 4.5.

Listing 4.11 XML Template Format

 <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"         sql:xsl="XSL FileName" >    <sql:header>      <sql:param>..</sql:param>      <sql:param>..</sql:param>...n    </sql:header>    <sql:query>      sql statement(s)    </sql:query>    <sql:xpath-query mapping-schema="SchemaFileName.xml">      XPath query    </sql:xpath-query>  </ROOT>

Table 4.5. XML Template Elements

Element	Description
`<ROOT>`	This tag provides a single top-level element (also referred to as the root tag ) for the resulting XML document. It can have any name .
`<sql:header>`	This tag is used to hold any header values. In the current implementation of SQL Server 2000, only the `<sql:param>` element can be specified in this tag. The `<sql:header>` tag acts as a containing tag, enabling you to define multiple parameters. This provides greater efficiency because all the parameter definitions are in one place. This is similar to declaring variables at the start of a T-SQL stored procedure.
`<sql:param>`	This element defines parameters that are passed to the queries inside the template. Each `<param>` element defines one parameter. Multiple `<param>` elements can be specified in the `<sql:header>` tag.
`<sql:query>`	This element specifies SQL queries.You can have multiple `<sql:query>` elements in a template. If there are multiple `<sql:query>` tags in the template and one fails, the others will proceed.
`<sql:xpath-query>`	This element specifies an XPath query. The schema filename must be specified using the `mapping-schema` attribute. If there are multiple `<sql:XPath-query>` tags in the template and one fails, the others will proceed.
`<sql:xsl>`	Specifies an XSLT stylesheet to be applied to the result document. A relative or absolute path can be given for the file. If a relative path is given, it is relative to the directory that was defined as the Template directory with theVirtual Directory Management utility.
`mapping-schema`	If you are executing an XPath query in a template, this attribute identifies the associated XDR schema. It can have a specified path identical to the path requirements of the `sql:xsl` element.

Here are some examples of using templates and template files in URLs. I'll reuse some of the earlier examples of URL SQL queries to illustrate the differences.

Here is a simple SELECT statement on a single table specified directly in a URL:

 http://iisserver/Nwind?template=<ROOT+xmlns:sql="urn:schemas-microsoft- com:xml-sql"><sql:query>SELECT+LastName,FirstName+FROM+Employees+  FOR+XML+AUTO</sql:query></ROOT>

Here is the result in Listing 4.12.

Listing 4.12 Specifying a Template Directly in a URL

 <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <Employees LastName="Davolio" FirstName="Nancy" />    <Employees LastName="Fuller" FirstName="Andrew" />    <Employees LastName="Leverling" FirstName="Janet" />    <Employees LastName="Peacock" FirstName="Margaret" />    <Employees LastName="Buchanan" FirstName="Steven" />    <Employees LastName="Suyama" FirstName="Michael' />    <Employees LastName="King" FirstName="Robert" />    <Employees LastName="Callahan" FirstName="Laura" />    <Employees LastName="Dodsworth" FirstName="Anne" />  </ROOT>

Taking the same template and making it a template file enables us to write it in a manner that is much easier to read (see Listing 4.13).

Listing 4.13 SQL Query Rewritten into a Template Format

 <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <sql:query>      SELECT LastName, FirstName      FROM Employees      FOR XML AUTO    </sql:query>  </ROOT>

Assuming that this template would be saved as the file template1.xml and saved to the directory with the virtual name templates, we would execute this template using the following URL:

 http://iisserver/Nwind/templates/template1.xml

Let's look at one more example. When we queried a combination of three tables, we ended up with the following URL:

 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

Converting this to a template file gives us Listing 4.14.

Listing 4.14 Long SQL Query Rewritten in a Template File

 <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <sql:query>      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    </sql:query>  </ROOT>

This template file will produce the same results as those shown in Listing 4.6, but don't you think this is easier to read than the URL method?

Passing Template Parameters

Just as we passed parameters to SQL queries, we can also pass them to templates. The <sql:header> element is used to define the parameters, which also can be assigned default values. These default values are used for parameters at run-time if values are not explicitly specified.

Explicit Default Values and Parameter Passing

In this example, we want the CustomerID, OrderID, RequiredDate, and freight costs for a CustomerID we specify in the URL. Take a close look at the template file in Listing 4.15. We have our query stated in the <sql:query> element as we would expect. In addition, we have explicitly specified a default value of VINET for the CustomerID. The <sql:param> element accomplishes this. The sql:header element holds all parameters and their values.

The item in the query that is the parameterized quantity is specified by prepending the @ symbol to the quantity name. In this case, it is CustomerID. Don't confuse this @ symbol usage with the XML attribute usage of @ . In this case, they are different entities altogether. This usage is specific to Microsoft parameterized expressions. If we execute this template file with the following URL, we will generate the result given in Listing 4.16.

 http://iisserver/Nwind/templates/customer.xml

Listing 4.15 Customer.xml

 <ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>    <sql:header>      <sql:param name='CustomerID'>VINET</sql:param>    </sql:header>    <sql:query>      SELECT CustomerID,OrderID,RequiredDate,Freight      FROM Orders      WHERE CustomerID=@CustomerID      FOR XML AUTO    </sql:query>  </ROOT>

Listing 4.16 Customer.xml Results with no CustomerID Passed

 <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <Orders CustomerID="VINET" OrderID="10248" RequiredDate="1996-08-    01T00:00:00" Freight="32.38" />    <Orders CustomerID="VINET" OrderID="10274" RequiredDate="1996-09-    03T00:00:00" Freight="6.01" />    <Orders CustomerID="VINET" OrderID="10295" RequiredDate="1996-09-    30T00:00:00" Freight="1.15" />    <Orders CustomerID="VINET" OrderID="10737" RequiredDate="1997-12-    09T00:00:00" Freight="7.79" />    <Orders CustomerID="VINET" OrderID="10739" RequiredDate="1997-12-    10T00:00:00" Freight="11.08" />  </ROOT>

Because no value for the parameter CustomerID was passed in the URL, the template file will use the default value VINET . If we pass a parameter value of WELLI , we obtain the results in Listing 4.17. Here's the URL:

 http://iisserver/Nwind/templates/customer.xml?CustomerID=WELLI

Listing 4.17 shows the results.

Listing 4.17 Partial Results with Parameter of CustomerID=WELLI

 <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <Orders CustomerID="WELLI" OrderID="10256" RequiredDate="1996-08-    12T00:00:00" Freight="13.97" />    <Orders CustomerID="WELLI" OrderID="10420" RequiredDate="1997-02-    18T00:00:00" Freight="44.12" />    <Orders CustomerID="WELLI" OrderID="10585" RequiredDate="1997-07-    29T00:00:00" Freight="13.41" />  ...  </ROOT>

Passing Multiple Parameters

You would think that multiple parameter passing would present no new problems, and you would be right. The parameters can just be individually listed in the <sql:header> element and be given default values. See Listing 4.18 and the result in Listing 4.19.

Listing 4.18 Shipvia.xmlMultiple Parameters in a Template

 <ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>    <sql:header>      <sql:param name='ShipVia'>1</sql:param>      <sql:param name='ShipCountry'>France</sql:param>    </sql:header>    <sql:query>      SELECT TOP 4 CustomerID,OrderID,Freight      FROM Orders      WHERE ShipVia=@ShipVia      AND ShipCountry=@ShipCountry      ORDER BY OrderID      FOR XML AUTO    </sql:query>  </ROOT>

Listing 4.19 Results of Listing 4.18

 <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <Orders CustomerID="VICTE" OrderID="10251" Freight="41.34" />    <Orders CustomerID='BLONP" OrderID="10265" Freight="55.28" />    <Orders CustomerID="VINET" OrderID="10274" Freight="6.01" />    <Orders CustomerID="BONAP" OrderID="10331" Freight="10.19" />  </ROOT>

Executing the URL http://iisserver/Nwind/templates/shipvia.xml , which calls the template in Listing 4.18, gives the results in Listing 4.19 because no parameters were passed and the default values of 1 for ShipVia and France for ShipCountry were used. You could also pass just one of the parameters. It would be substituted for the default value, and the other parameter would use the default value provided.

Specifying an XSL Stylesheet

There also is really nothing new when you want to apply an XSLT stylesheet to the results of a template file. Just specify the stylesheet name in the sql:xsl attribute of the ROOT element. Let's take the example from "The XSL Keyword" section earlier in this chapter that illustrated using an XSLT stylesheet.

Stylesheets are developed utilizing some third-party (relative to SQL server) application using a model of the document it is supposed to transform. If everything is tested during development, the only error conditions that usually happen are a blank HTML document, garbled HTML output, or an attempted transformation of an XML document that ends up blank. Because the XSLT stylesheet and query are properly tested in development, almost all these errors are traceable to a bad XML document or bad data.

Listing 4.20 gives the template file XSLDemo.xml.

Listing 4.20 XSLDemo.xml

 <ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'  sql:xsl="XSLDemo.xml">    <sql:query>      SELECT TOP 4 OrderID,EmployeeID,Shipname      FROM Orders      WHERE EmployeeID=5      FOR XML AUTO    </sql:query>  </ROOT>

The stylesheet to apply is given in Listing 4.9, and the results are given in Listing 4.10. These result in Table 4.4.