XML and SQL Server 2000

2017-07-07 02:10:07

As a last coding illustration in this chapter, I want to present a real-world example. This example was taken from a corporate intranet site, and it generates a quarterly fiscal report for the company in an HTML tabular format. The table we want to create is shown in Figure 2.4. The XML data file and stylesheet are available for download at http://www.newriders.com.

Figure 2.4. Quarterly fiscal report in HTML format.

Listing 2.28 shows the XML data file Channels.xml .

Listing 2.28 Channels.xml Document

Figure 2.4 shows the HTML table we want to end up with.

XSLT Stylesheet

Listing 2.29 shows the XSLT stylesheet used to generate the table. This is a very long example, but it is much more attune to the real world. I've attempted to use as many XSLT elements as possible to give you good examples of their use. And remember one thing: There is no one way to write a stylesheet! Some ways are more efficient than others, but first get the correct answer.

One thing in this stylesheet that I did not cover explicitly in the book is the use of variables. Just as I gave you some exercises in Chapter 1 to work out on your own, I purposely left variables out of the discussion to give you some practice in looking up definitions.

I again refer you to Figure 2.4 for the table that this stylesheet generates.

Listing 2.29 The Stylesheet That Generates the HTML Corporate Financial Report

<?xml version='1.0'?> <xsl:stylesheet xmlns:xsl='http://www.w3.org/XSL/Transform/1.0'> <xsl:output method="html" indent="yes"/> <xsl:decimal-format/>  <xsl:template match="/"> <HTML> <BODY> <TABLE border='0' cellPadding='0' cellSpacing='0' width='100%'> <TR> <TD colspan='14' align='middle' bgColor='#ffffff' height='20'> Bookings and Orders Report</TD> </TR> <TR> <TD colspan='14' align='middle' bgColor='#ffffff' height='20'> Report as of - 2000-Jul-27</TD> </TR> </TABLE> <TABLE border='1' cellPadding='0' cellSpacing='0' width='100%'> <TR> <TD bgColor='#000000' height='18'> <xsl:text> </xsl:text> </TD> <TD bgColor='#000000'> <xsl:text> </xsl:text> </TD> <TD align='middle' bgColor='#000000' colSpan='2'> Bookings </TD> <TD align='middle' bgColor='#000000' colSpan='2'> Returns </TD> <TD align='middle' bgColor='#000000' colSpan='2'> Backlog </TD> <TD align='middle' bgColor='#000000' colSpan='6'> Future Orders</TD> </TR> <TR> <TD align='right' bgColor='#000000' height='15'> Channel </TD> <TD align='middle' bgColor='#d1d1d1'> Region</TD> <TD align='middle' bgColor='#d1d1d1'> Day</TD> <TD align='middle' bgColor='#d1d1d1'> MTD</TD> <TD align='middle' bgColor='#d1d1d1'> Day</TD> <TD align='middle' bgColor='#d1d1d1'> MTD</TD> <TD align='middle' bgColor='#d1d1d1'> > 7 Days</TD> <TD align='middle' bgColor='#d1d1d1'> 1-7 Days</TD> <TD align='middle' bgColor='#d1d1d1'> Curr Wk</TD> <TD align='middle' bgColor='#d1d1d1'> 7/31/00</TD> <TD align='middle' bgColor='#d1d1d1'> 8/7/00</TD> <TD align='middle' bgColor='#d1d1d1'> 8/14/00</TD> <TD align='middle' bgColor='#d1d1d1'> >08/21/00</TD> <TD align='middle' bgColor='#d1d1d1'> Curr Qtr</TD> </TR> <xsl:apply-templates/> </TABLE> </BODY> </HTML> </xsl:template> <xsl:template match="CHANNEL"> <TR> <TD align='right' bgColor='#000000' height='15'> <xsl:value-of select='@TYPE'/> </TD> <xsl:apply-templates select='AREA'/> </TR> </xsl:template> <xsl:template match="AREA"> <xsl:choose> <xsl:when test="'Americas'=@LOCATION"> <TD align='right' bgColor='#ffffff'> Americas </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="BOOKINGS/DAY"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="BOOKINGS/MTD"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="RETURNS/DAY"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="RETURNS/MTD"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="BACKLOG/GT_7DAYS"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="BACKLOG/LT_7DAYS"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right" bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/CURRWK"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/WK7-31-00"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/WK8-7-00"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/WK8-14-00"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/WK_GT_8-21-00"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/CURR_QTR"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> </xsl:when> <xsl:otherwise> <TR> <TD bgColor='#000000'></TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="location" select="@LOCATION"/> <xsl:value-of select="@LOCATION"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="BOOKINGS/DAY"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="BOOKINGS/MTD"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="RETURNS/DAY"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="RETURNS/MTD"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="BACKLOG/GT_7DAYS"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="BACKLOG/LT_7DAYS"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name='amount' select="FUTURE_ORDERS/CURRWK"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name='amount' select="FUTURE_ORDERS/CURRWK"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name='amount' select="FUTURE_ORDERS/WK8-7-00"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/WK8-14-00"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/WK_GT_8-21-00"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> <TD align='right' bgColor='#ffffff'> <xsl:variable name="amount" select="FUTURE_ORDERS/CURR_QTR"/> <xsl:value-of select="format-number($amount, '#,#00;(#,#00)')"/> </TD> </TR> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet>

These examples should give you plenty to study and should firm up a lot of what we've talked about in this chapter. Most importantly, have fun.

Figure 2.4. Quarterly fiscal report in HTML format.

Listing 2.28 Channels.xml Document

XSLT Stylesheet

Listing 2.29 The Stylesheet That Generates the HTML Corporate Financial Report

Категории