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:header> <sql:param name="CustomerID">ANTON</sql:param> </sql:header> <sql:query> <![CDATA[ --SQL Comments work here too! SELECT TOP 10 Employee.LastName + ', ' + Employee.FirstName EmployeeName, OrderID, convert(varchar(11),RequiredDate) RequiredDate, [Order].CustomerID 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 ]]> </sql:query> </OrdersByEmployee>
Now take a look at the special tags used inside XML templates. They include the following:
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.