HandsOn Creating a Custom Transformation File

1. Open Notepad and enter the statements as shown below. <?xml version="L0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="html" version="4.0" indent="yes"/>

<xsl:template match="dataroot"> <html> <body>

<h2><font name="Verdana">Orders by Customer</font></h2> <p></p>

<xsl:apply-templates select="Customers"/>

<xsl:template match="Customers"> <table> <tr>

<td BgColor="#FFCC33"> <font color="#000000">

<xsl:value-of select="CustomerID"/> </font> </td> <td><b>

<xsl:value-of select="CompanyName"/> </b></td> </tr> </table>

<table cellpadding="5" cellspacing="5"> <tr BgColor="black">

<td bgcolor="black" width="10px"></td> <td><font color="white" size="1">Order ID</font></td> <td><font color="white" size="1">Order Date</font></td> <td><font color="white" size="1">Shipped Date</font></td> <td><font color="white" size="1">Required Date</font></td> <td><font color="white" size="1">Freight</font></td> </tr>

<xsl:apply-templates select="Orders"/> </table> </xsl:template>

Taking Your VBA Programming Skills to the Web

<xsl:template match="Orders"> <tr>

<td bgcolor="black" width="10px"></td> <td><xsl:value-of select="OrderID"/></td> <td><xsl:value-of select="substring(OrderDate, 1, 10)"/></td> <td><xsl:value-of select="substring(ShippedDate, 1, 10)"/></td> <td><xsl:value-of select="substring(RequiredDate, 1, 10)"/></td> <td>$<xsl:value-of select="format-number(Freight,'####0.00')"/></td> </tr> </xsl:template>

</xsl:stylesheet>

2. Save the file as ListCustOrders.xsl in the Learn_XML folder. You must type the file extension to ensure that the file is not saved as text.

3. Close Notepad.

Let's now proceed to analyze the contents of the ListCustOrders.xsl file that will be used to transform XML to HTML in our next hands-on exercise. Notice that because the XSLT stylesheet is an XML document, we started out with a standard XML declaration:

<?xml version="L0" encoding="UTF-8"?>

Next, we defined the namespace for the stylesheet and declared its prefix like this:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

On the third line we indicated that XSLT should transform the XML into HTML by using the <xsl:output> tag as follows:

<xsl:output method="html" version="4.0" indent="yes"/>

Notice that because the <xsl:output> tag is always empty, it must be terminated with a forward slash (/). This tag has two attributes: method and indent. The method attribute specifies the format of the output. This can be XML, HTML, or text. The indent attribute, which is set to "yes" in this example, indicates that the XML should be indented. This will make the final XML document more readable when viewed in the browser.

The remaining part of the XSL file contains transformation instructions for the XML document element nodes. We begin by creating the root template. The <xsl:template> tag initiates a template within a stylesheet. Because a template must indicate which nodes you want to use, we supplied the node information by using the tag's match attribute, like this:

<xsl:template match="dataroot">

This tells the XSLT processor to extract the XML document's root node. The root node provides a base node upon which we will build our web page. Notice that in the root template we included the <html> and <body> tags to create the structure of the final document and used HTML tags such as <h2>, <font>, and <p> to add the required formatting to our web page. In the root template we are also telling the XSLT processor that it should apply the template rules found in the Customers template (defined further down):

Part V

<xsl:apply-templates select="Customers"/>

Notice that the above instruction is placed within the <table> and </table> tags because we want to lay out the customer information using the table structure. When the XSLT processor encounters the <xsl:apply-templates> instruction, it will proceed to the following line:

<xsl:template match="Customers">

This line marks the beginning of the Customers template rule. Within it there are HTML tags as well other XSLT processing instructions. For example, to output the CustomerID we use the <xsl:value-of> tag with the select attribute like this:

<xsl:value-of select="CustomerID"/>

Because the <xsl:value-of> tag does not have any content, you must end it with the forward slash (/). Notice that we placed the value of the CustomerID field in a table cell. Using the same approach we output the CompanyName:

<xsl:value-of select="CompanyName"/>

Next, we defined the column headings for the Orders table. For a special effect, we added to the output a 10-pixel-wide dummy column with a black background:

<td bgcolor="black" width="10px"></td>

We also told the XSLT processor to apply the Orders template:

<xsl:apply-templates select="Orders"/>

The Orders template rules indicate how to extract values for each of the defined column headings. This is done by using the <xsl:value-of> tag with the select attribute, like this:

<td><xsl:value-of select="OrderID"/></td> <td><xsl:value-of select="substring(OrderDate, 1, 10)"/></td> <td><xsl:value-of select="substring(ShippedDate, 1, 10)"/></td> <td><xsl:value-of select="substring(RequiredDate, 1, 10)"/></td> <td>$<xsl:value-of select="format-number(Freight,'####0.00')"/></td>

To obtain only the date portion from the OrderDate, ShippedDate, and RequiredDate columns we use the XPath substring function in the select attribute. This function has the same syntax as the VBA Mid function, allowing you to extract a specified number of characters from a string starting at a specific position. The format of the substring function is shown below:

substring(string, startpos, length)

startpos is the position of the first character to extract, and length represents the number of characters to be returned from string. Therefore, the expression

<xsl:value-of select="substring(OrderDate, 1, 10)"/>

tells the XSLT processor to retrieve only the first 10 characters from the value found in the OrderDate column.

Taking Your VBA Programming Skills to the Web

Notice also that to correctly format the Freight column we used the format-number XPath expression like this:

<xsl:value-of select="format-number(Freight,'####0.00')"/>

This tells the XSLT processor to format the value found in the Freight column as a number using two decimal places. Notice that the dollar sign cannot be a part of the XPath expression. It is appended to the final output as shown below:

<td>$<xsl:value-of select="format-number(Freight,'####0.00')"/></td>

Notice that each of the defined template rules ends with the </xsl:template> ending tag and the stylesheet itself ends with the </xsl:stylesheet> tag.

This concludes our hands-on example of how you can make your own custom stylesheets. While this is a basic stylesheet to get you started, in real life you will probably want to create stylesheets that allow:

Batch-processing nodes (<xsl:for-each> tag with the select attribute) Conditional processing of nodes (<xsl:if> tag with the test attribute) Decisions based on conditions (<xsl:choose> tag and <xsl:when> tag with the test attribute)

Sorting nodes before processing (<xsl:sort> tag with the select attribute).

^^ Note: For more information about XSL Transformations (XSLT), visit the following link: http://www.w3.org/TR/xslt#section-Applying-Template-Rules

Now that you have a custom stylesheet, what do you do with it? Hands-On 29-6 demonstrates how to export data from an Access table directly to an HTML file and apply a custom transform so that only certain columns are displayed in the browser.

0 0

Post a comment