Applying Style Sheets with Templates | Programming Microsoft SQL Server(TM) 2000 with XML (Pro-Developer)

When you use a template to retrieve data, you might need to apply an XSL style sheet to the XML returned. An XSL style sheet contains instructions for presenting the XML data in a different format such as HTML or WML, or it might be used to transform the data from one XML grammar to another.

The most common use of an XSL style sheet is to render the data as HTML for display in a browser. For example, the Northwind Traders catalog could be published on a Web site by applying XSL style sheets to the XML data retrievedby the queries in the templates. The style sheet used with the categories.xml template would need to display the list of categories and provide hyperlinks to the products.xml template that includes the categoryid parameter. You could use the following style sheet to render the category list as an HTML table:

 <?xml version=‘1.0’?> <xsl:stylesheet xmlns:xsl=     ‘http://www.w3.org/1999/XSL/Transform’ version='1.0'>     <xsl:template match=‘/’>         <BODY>             <TABLE>                 <TR>                     <TD><B>Click a Product Category</B></TD>                 </TR>                 <xsl:for-each select=‘categorylist/categories’>                     <TR>                         <TD>                             <A>                                 <xsl:attribute name=‘HREF’>                                     products.xml?categoryid=                                     <xsl:value-of select=‘categoryid’/>                                 </xsl:attribute>                                 <xsl:value-of select=‘categoryname’/>                             </A>                         </TD>                     </TR>                 </xsl:for-each>             </TABLE>         </BODY>     </xsl:template> </xsl:stylesheet>

This style sheet is also available in the Demos\Chapter5\Templates folder on the companion CD in the file named categories.xsl. Notice that the code uses the categoryid parameter in the construction of the hyperlink to the products.xml template. This tactic enables the appropriate parameter to be passed to the products.xml template when the user clicks the desired category name.

The product list itself could be rendered as HTML using a style sheet similar to the following example:

 <?xml version=‘1.0’ ?> <xsl:stylesheet xmlns:xsl=     ‘http://www.w3.org/1999/XSL/Transform’ version='1.0'>     <xsl:template match=‘/’>         <HTML>             <BODY>                 <H2>Product List</H2>                 <TABLE>                     <TR>                         <TD><B>Product</B></TD>                         <TD><B>Price</B></TD>                     </TR>                     <xsl:for-each select=‘productlist/products’>                     <TR>                         <TD><xsl:value-of select=‘productname’/></TD>                         <TD><xsl:value-of select=‘unitprice’/></TD>                     </TR>                     </xsl:for-each>                 </TABLE>             </BODY>         </HTML>     </xsl:template> </xsl:stylesheet>

You can also find this style sheet in the Demos\Chapter5\Templates folder on the companion CD in the file named products.xsl.

Applying a Style Sheet on the Server

Creating the appropriate style sheets is only a part of the solution. You must also provide a way for the application to apply the correct style sheet when the data from a template has been retrieved. For Web pages on an Internet site, it is usually best to apply the style sheet on the server and return HTML to the browser, as shown in Figure 5-1.

Figure 5.1 - Server-side XSL processing

Using the XSL Attribute

To apply the products.xsl style sheet to the data returned by the products.xml template, you could save the style sheet in the template virtual name (or a subfolder) and reference it in the template using an xsl attribute in the root element, as shown in the example below.

 <?xml version=‘1.0’ ?> <productlist xmlns:sql=‘urn:schemas-microsoft-com:xml-sql’     sql:xsl=‘products.xsl’>     <sql:header>         <sql:param name=‘categoryid’>1</sql:param>     </sql:header>     <sql:query>         SELECT productid, productname, unitprice         FROM products         WHERE categoryid = @categoryid         FOR XML AUTO, ELEMENTS     </sql:query> </productlist>

This template is saved as ProductListServerXSL.xml in the Demos\ Chapter5\Templates folder on the companion CD. The addition of the xsl attribute causes the OLE-DB provider to apply the style sheet and return the transformed data to the client. In this case, the result is an HTML document. However, you should be particularly aware of one issue when you’re using the xsl attribute: the problem of content types. By default, templates return documents with a content type of text/xml. An XML-aware browser treats any document of this type as an XML document and attempts to parse and display it. Because an HTML document generated from a style sheet can, in fact, be a valid XML document, the browser parses and displays the document instead of rendering the HTML. To overcome this obstacle, you must specify a contenttype parameter of text/html in a query string when using a URL to access a template. For example, the URL used to retrieve the category list in HTML format would be:

 http://webserver1/northwinddata/onlinesales/CategoryListServerXSL.xml     ?contenttype=text/html

You can execute this query by opening the shortcut named Category List (ServerSideXSL) in the Demos\Chapter5 folder on the companion CD. Because the categories.xsl style sheet contains hyperlinks to the products.xml template, you also need to modify the links in the categories.xslstyle sheet to include the contenttype parameter, as shown here:

      <A>          <xsl:attribute name=‘HREF’>              ProductListServerXSL.xml?categoryid=<xsl:value-of                   select=‘categoryid’/>              &amp;contenttype=text/html          </xsl:attribute>          <xsl:value-of select=‘categoryname’/>      </A>

You can find the complete style sheet in the file named CategoryListServer.xsl in the Demos\Chapter5\Templates folder.

Because the ampersand has a particular meaning in an HTML document, any query strings containing multiple parameters must use & in place of &.

Figure 5-2 shows the output after the URL executed the categories.xml template.

Figure 5.2 - The category list page

If you click the category name you want on the category list page, the product list page will open, as shown in Figure 5-3.

Figure 5.3 - The product list page

Specifying a Style Sheet in the URL

You can also apply a style sheet on the server by specifying an xsl parameter in the URL that you’re using to access the template. In this case, you must save the style sheet in the virtual root directory or in one of its subdirectories. You must specify the relative path if you store the style sheet in a subdirectory of the virtual directory. For example, you could make use of the following URL to retrieve data from the categories.xml template and apply the categories.xsl style sheet:

 http://webserver1/northwinddata/onlinesales/categories.xml     ?xsl=CategoryListServer.xsl

You can execute this query by opening the shortcut named Category List (Style Sheet in URL) in the Demos\Chapter5 folder on the companion CD. Because the categories.xsl style sheet formats the data as HTML, the content type defaults to text/html so that the browser can display the HTML page properly. If a template includes an xsl attribute and a different style sheet is specified in the URL, the style sheet referenced by the xsl attribute in the template is applied first; the style sheet specified in the URL is then applied to the resulting document.

Applying a Style Sheet on the Client Side

You can take a different approach if the client supports XML. By referencing the style sheet in a processing instruction, you can download the XML data to the client and the style sheet can then be accessed and applied. The processing instruction declares the location of the style sheet in the template. Figure 5-4 illustrates this approach.

Figure 5.4 - Client-side XSL processing

To apply the products.xsl style sheet to the XML data retrieved by the products.xml template, you need to add a processing instruction to the top of the template after the XML declaration, as shown in the following example:

 <?xml version=‘1.0’ ?> <?xml-stylesheet type=‘text/xsl’ href=‘products.xsl’?>     <productlist xmlns:sql=‘urn:schemas-microsoft-com:xml-sql’>     <sql:header>         <sql:param name=‘categoryid’>1</sql:param>     </sql:header>     <sql:query>         SELECT productid, productname, unitprice         FROM products         WHERE categoryid = @categoryid         FOR XML AUTO, ELEMENTS     </sql:query> </productlist>

This template is saved as ProductListClientXSL.xml in the Demos\Chapter5\ Templates folder on the companion CD. If you’re equipped with Microsoft Internet Explorer 5, you could access the product list using this template. In this case, the XML parser on the client interprets the processing instruction and the style sheet is then downloaded and processed. This approach has the advantage of removing some of the processing load from the server but requires an additional network trip to download the style sheet. Of course, you also need to consider that the client must be XML-enabled or the style sheet will never be applied, which makes this approach most suitable for internal applications or intranet sites. For Internet-based applications, you’re more likely to find that server-side XSL processing is the best option.