Specifying an XSL Style Sheet in a Template
An Extensible Stylesheet Language (XSL) style sheet can be applied to the query results. When you execute a template using HTTP, you can specify an XSL file in these ways:
- Use the sql:xsl attribute in the template.
- Use the xsl keyword as part of the URL to specify the XSL file that will be used to process the resulting XML data.
If the XSL file is specified both in the template using sql:xsl and in the URL using the keyword xsl, the XSL style sheet specified in the template is applied to the results first, and then the XSL file specified in the URL is applied.
Examples
In the following example, nwind is a virtual directory created using the IIS Virtual Directory Management for SQL Server utility, and template is the virtual name of template type defined when the virtual directory is created (any name can be given to a virtual name when it is created). For more information, see Using IIS Virtual Directory Management for SQL Server Utility.
A. Specify sql:xsl in a template to process the result
In this example, a template includes a simple SELECT statement. The query result is processed according to the instructions in the XSL file specified using sql:xsl.
<?xml version ='1.0' encoding='UTF-8'?>
<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'
sql:xsl='MyXSL.xsl'>
<sql:query>
SELECT FirstName, LastName FROM Employees FOR XML AUTO
</sql:query>
</root>
For illustration purposes, the template (TemplateWithXSL.xml) is stored in the directory associated with the virtual name (template), of template type. The XSL file (MyXSL.xsl) is also stored in the same directory.
This is the XSL file:
<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match = '*'>
<xsl:apply-templates />
</xsl:template>
<xsl:template match = 'Employees'>
<TR>
<TD><xsl:value-of select = '@FirstName' /></TD>
<TD><B><xsl:value-of select = '@LastName' /></B></TD>
</TR>
</xsl:template>
<xsl:template match = '/'>
<HTML>
<HEAD>
<STYLE>th { background-color: #CCCCCC }</STYLE>
</HEAD>
<BODY>
<TABLE border='1' style='width:300;'>
<TR><TH colspan='2'>Employees</TH></TR>
<TR><TH >First name</TH><TH>Last name</TH></TR>
<xsl:apply-templates select = 'root' />
</TABLE>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>
This URL executes the template:
http://IISServer/nwind/template/TemplateWithXSL.xml&contenttype=text/html
Because the XSL file is applied to the result, the contenttype is set to text/html. Therefore, specifying the contenttype parameter in the URL is optional.
The result is displayed in a two-column table format (FirstName and LastName).
You can also specify the XSL file in the URL instead of in a template (using sql:xsl), . In this case, the XSL file must be stored in the directory associated with the virtual root (nwind) or one of its subdirectories, in which case the relative path must be specified in the URL. Assuming the XSL file is stored in the directory associated with the nwind virtual directory, this URL executes the template:
http://IISServer/nwind/template/templateFile.xml?xsl=MyXSL.xsl
If the XSL file is stored in a subdirectory (x) of the virtual root directory, the URL with a relative path is specified as:
http://IISServer/nwind/template/templateFile.xml?xsl=/x/MyXSL.xsl
If the XSL file is specified in the template using sql:xsl and in the URL using the keyword xsl, the XSL style sheet specified in the template is applied to the results first, and then the XSL file specified in the URL is applied.