Understanding Query Templates
 
In the article titled Customizing URL Queries, we saw how to define a simple template query and execute it from the URL. What we did not do in that article was to define what a template is and what its constituents are. This is what we will do in this article.
 
Here is a simple template.

<Titles xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
	<sql:query>
		SELECT title_id, title, type FROM titles
			FOR XML AUTO, ELEMENTS
	</sql:query>
</Titles>

The following are the points to be remembered about templates:

  • A template query has a root element (identified as "Titles" as above).
  • All template queries need a namespace that points to "urn:schemas-microsoft-com:xml-sql". In our example, the namespace is called "sql".
  • Any query that needs to be executed is specified within the "sql:query" tag.
  • All template files should be saved in the folder identified as "template" in the IIS Configuration for SQL Server.

Once a template query is defined, it can be executed via the URL as shown.


http://localhost/pubs/template/GetAllTitles.xml

Note that for a URL query we just specify the template file. A template file can also specify multiple queries, as shown:


<Titles xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
	<sql:query>
		SELECT title_id, title, type FROM titles
			FOR XML AUTO
			
		SELECT au_id, au_fname, au_lname FROM authors
			FOR XML AUTO
	</sql:query>
</Titles>

In the above template, we define two queries in the template and when executed, the results for both the queries are appended one below the other. Template queries can also accept parameters as shown:


<Titles xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
	<sql:header>
		<sql:param name='title_id'>%</sql:param>
	</sql:header>
	<sql:query>
		SELECT title_id, title, type FROM titles
			WHERE title_id LIKE @title_id
			FOR XML AUTO
	</sql:query>
</Titles>

There are some changes that you can notice here:

  • We have added a new node called "sql:header". This node is used to specify all the parameters that a query can take.
  • Each parameter is identified by "sql:param".
  • Each parameter has a name (title_id in the above example) and a default value (% in the above example).
  • The parameter thus defined is used in the query specified within "sql:query". Note the usage of the @title_id in the query. The @ is the SQL Server syntax for specifying a parameter.

Having specified the parameter, the template can now be executed in the following ways:


http://localhost/pubs/template/GetAllTitles.xml?title_id=BU1032
http://localhost/pubs/template/GetAllTitles.xml

In the first method, we pass a parameter to the query and in the second method, the default value of the parameter is used. You can also customize the output from a template query by specifying an XSL file (used to format the XML as HTML). Here is an example of a style-sheet that is customized for handling the above query.


<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:template match="titles">
		<TR>
			<TD><xsl:value-of select='@title_id'/></TD>
			<TD><xsl:value-of select='@title'/></TD>
			<TD><xsl:value-of select='@type'/></TD>
		</TR>
	</xsl:template>
	<xsl:template match="/">
		<HTML>
			<BODY>
				<TABLE BORDER="1">
					<TR>
						<TH>Title ID</TH>
						<TH>Name</TH>
						<TH>Type</TH>
					</TR>
					<xsl:apply-templates select='Titles/titles'/>
				</TABLE>
			</BODY>
		</HTML>
	</xsl:template>
</xsl:stylesheet>

Once a stylesheet has been defined, it can be referenced in the URL as follows:


http://localhost/pubs/template/GetAllTitles.xml?xsl=template/GetAllTitles.xsl&contenttype=text/html

Note that we specify an "xsl" parameter and also a "contenttype" parameter. The "contenttype" is required since we expect the output as HTML after application of the stylesheet. This URL produces an output as shown in the following figure (results abridged...)

The XSL can also be specified in the template file. For an example of this, see the article titled Customizing URL Queries.

Home