Using XML Templates

An XML template is an XML document that contains T-SQL statements that are executed when you specify the template name in the URL. This is handy because you don't want to use URL queries when you have many lines of T-SQL code to execute. Also, if you uncheck the Allow URL Queries option from your Northwind virtual directory (using the IIS Virtual Directory Management for SQL Server tool as explained earlier), the security hole opened by URL queries is closed and URL access is limited to addresses that specify template filenames.

When a template filename is specified in a URL query, the SQL Server 2000 ISAPI filter executes the statements inside. The ISAPI filter uses the virtual name called Templates (or any other virtual names of type template) that you created earlier to run the template files stored in the Template subdirectory (see Figure 41.13).

Figure 41.13. NorthwindVdir templates path.


Listing 41.13 shows an example of a simple XML template. Save its code in a file called OrdersByEmployee.xml to the Template subdirectory (this code can also be found on the CD-ROM with this book).

Listing 41.13 A Simple XML Template
<OrdersByEmployee xmlns:sql="urn:schemas-microsoft-com:xml-sql">
              <sql:param name="CustomerID">ANTON</sql:param>
              --SQL Comments work here too!
              SELECT TOP 10 Employee.LastName + ', ' +
                              Employee.FirstName EmployeeName,
                              convert(varchar(11),RequiredDate) RequiredDate,
               FROM    Orders [Order]
               JOIN Customers C on C.CustomerID = [Order].CustomerID
               AND C.CustomerID = @CustomerID
               JOIN Employees Employee on
                      Employee.EmployeeID = [Order].EmployeeID
               ORDER BY EmployeeName, RequiredDate
               FOR XML AUTO

Now take a look at the special tags used inside XML templates. They include the following:

  • A root element of any name. (Listing 41.13 uses Customers). The root element must specify the namespace attribute xmlns:sql="urn:schemas-microsoft-com:xml-sql". It also supports the optional attribute sql:xsl whose value is a relative or absolute path to an XSL stylesheet. SQL Server will transform and output the results of the template query using the specified stylesheet before returning it to its calling object.

  • sql:query? This is the enclosing element for all your T-SQL statements. It can be repeated to organize statements into groups.


Enclose your sql:query statements and sql:param values between <![CDATA[ and ]]> (known as a CDATA section) to avoid having to manually encode any special characters. This makes things much easier because it instructs the parser to treat comparison characters such as < as less-than and not as an indicator of the start of an XML element. For reference, the special characters you need to encode outside of a CDATA section are <, >, &, ', and ". Convert them to the strings (known as entities) &lt;, &gt;, &amp;, &apos;, and &quot; when you need to use them as element or attribute values.

  • sql:header? The enclosing element for any sql:param tags. Not repeatable.

  • sql:param? A repeatable element used to define any input parameters (named name) you want to pass from the URL's query string into the statements in sql:query.

  • sql:xpath-query? Specify an XPath query in the template.

Execute OrdersByEmployee.xml using IE with the following URL:


The resulting XML is found in Figure 41.14.

Figure 41.14. The results of the template query http://<myserver>/NorthwindVdir/ templates/ OrdersByEmployee.xml?CustomerID=ALFKI.


The results also show how the default value of ANTON, the value of our sql:param tag, is overridden by passing in ALFKI as the value of the CustomerID parameter in the query string.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features