.NET Parameterized Templates

Templates combine the output of multiple queries and construct complex XML documents. They outline an XML format together with placeholders to fill with the results of SQL and/or XPath queries that returns XML. That’s already the whole idea of a template. Look at the template below for example:

<?xml version="1.0" encoding="utf-8" ?>

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <sql:query>

    select CustomerID, ContactName

      from Customers

      FOR XML AUTO, ELEMENTS

  </sql:query>

</ROOT>

The results of the query replace the <query> tag in the XML frame when the template executes. One possible result could look like this:

<?xml version="1.0" encoding="utf-8" ?>

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <Customer>

    <CustomerID>ALFKI</CustomerID>

    <ContactName>Maria Anders</ContactName>

  </Customer>

</ROOT>

Originally template queries were designed to run queries through HTTP requests. The template would be stored in file on a web server and clients retrieve query results simple by requesting the template file. By now templates can also be executed programmatically through COM objects or the SQLXML managed classes which we are going to discuss in section 15.2.4.

While executing the example above demonstrated the concept behind templates, it is not very useful for real world applications. The query above retrieves all records from the Customers table because we did not add any criteria restricting the query. How would we go about retrieving one specific customer record? Obviously templates would be useless if we had to code a different template for each customer record in the database, so that cannot be the right solution. The answer is: We add parameters to the template. Parameterized templates look similar to XSLT style sheets with parameters. If you think about it, they perform a similar function, too. We define the parameters in a separate header section and reference them throughout the template by their name. Template parameters are identified by a prefixing their name with a special character, just like parameters in an XSL style sheet. In the XML templates it’s the ‘@’ instead of the ‘$’, but the idea is the same. So let’s see what the template looks like after we added a CusID parameter to restrict the query to records matching the value of the parameter.

<?xml version="1.0" encoding="utf-8" ?>

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <sql:header>

     <sql:param name='CusID' />

  </sql:header>

  <sql:query>

    select CustomerID, ContactName

      from Customers Customer

      where CustomerID = @CusID

FOR XML AUTO, ELEMENTS

  </sql:query>

</ROOT>

How we pass the parameters to the template depends on the way we are invoking it. If we invoke it through the HTTP interface, then we add the parameters to query string of the request. The managed classes have a different way to pass parameters which we will hear about in section 15.2.4.2.

There are more details about templates we have not discussed, how we pass null values, for example. You can look them up in the SQL Server Books Online documentation or the MSDN web site if you think about employing query templates in your applications.