Display Cross Tab Table Example using XML, XPATH, XSLT(Transform)

profitloss2008.xml

<?xml version="1.0" encoding="utf-8" ?>
<?xml-stylesheet type="text/xsl" href="roundcrosstab.xsl"?>
<!--
GUID: sqlite-manager@sqlite-manager.googlecode.com
Homepage: http://sqlite-manager.googlecode.com
Generation Time: Fri, 25 Feb 2011 02:59:07 GMT
SQLite version: 3.7.1
-->
<!-- Database: demonstration.db -->
<demonstration.db>

<profitloss2008>
<Category>Gross Sales</Category>
<First_Qtr>42410.46000000001</First_Qtr>
<Second_Qtr>36290.93</Second_Qtr>
<Third_Qtr>29841.670000000002</Third_Qtr>
<Fourth_Qtr>25719.699999999997</Fourth_Qtr>
<Totals_for_2008>134262.76</Totals_for_2008>
</profitloss2008>

<profitloss2008>
<Category>Expenses</Category>
<First_Qtr>36105.89</First_Qtr>
<Second_Qtr>35809.12</Second_Qtr>
<Third_Qtr>33382.55</Third_Qtr>
<Fourth_Qtr>28798.77</Fourth_Qtr>
<Totals_for_2008>134096.33</Totals_for_2008>
</profitloss2008>

<profitloss2008>
<Category>Net Profit</Category>
<First_Qtr>6304.5700000000015</First_Qtr>
<Second_Qtr>481.8099999999995</Second_Qtr>
<Third_Qtr>-3540.880000000001</Third_Qtr>
<Fourth_Qtr>-3079.0700000000015</Fourth_Qtr>
<Totals_for_2008>166.42999999999847</Totals_for_2008>
</profitloss2008>

</demonstration.db>

Text of roundcrosstab.xsl

roundcrosstab.xsl

XSL Transform file to convert XML exported by SQLite Manager into a table within an HTML document. Negative numbers(losses) are printed using an inline stylesheet and a conditional statement which evaluates whether a number in the table is less than zero. Also includes a round function which is used to round dollar figures to two decimal places.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<HTML>
<HEAD>
<TITLE>SQLite XML File with XSLT </TITLE>
<meta http-equiv="Content-Type" content="utf-8" />

<!--Inline Style Sheet -->
<STYLE type="text/css">
BODY{background:#f0f8ff}
H1{font:bold 18pt "Courier New";color:black}
H3{font:bold 14pt "Courier New";color:black}
TD{font:normal 12pt "Courier New";color:black;text-align:right;padding:10px}
TD.red {font:normal 12pt "Courier New";color:red;text-align:right;padding:10px}
P{font:normal 12pt "Courier New";color:black}

HR{color:red}

</STYLE>

</HEAD>
<BODY>

<H1>Fictional Products Inc. </H1>
<H3>2008 Profit/Loss by Quarter </H3>
<!--Insert Crosstab Table Here -->
<xsl:apply-templates/>
<P>Data subject to revision.
</P>

<HR />

</BODY>
</HTML>
</xsl:template>

<!--Crosstab Table Template -->
<xsl:template match="/*">

<TABLE BORDER="0">
<!--First row of table -->
<TR>

<!-- Loop for column headings. -->
<xsl:for-each select="*[position() = 1]/*"> <!-- Match each record element tag. -->

<TH>
<xsl:value-of select="local-name()"/> <!-- Take element name as column heading -->
</TH>
</xsl:for-each> <!--End column heading loop -->
</TR>

<xsl:apply-templates/> <!--Insert remaining rows of table -->

</TABLE>
</xsl:template>

<xsl:template match="/*/*" > <!--Match each field or column name element tag. -->
<!--Loop for each record or row -->
<xsl:for-each select="*[position()=1]">
<TR>
<!--Display first field as row heading -->
<TH> <xsl:value-of select="text()[position()]" /> </TH>

<!--Loop for each field after the first field within the record or row -->
<xsl:for-each select="following-sibling::*" > <!--Select next field element -->

<!--Variable anum expected to be a real number. Round function in XSL, rounds to nearest integer. To round a dollar value to 2 decimal places, multiply text() by 100, round to integer and then divide by 100. -->
<xsl:variable name="anum" select= "(round(100*text())) div 100" />

<!--Conditional statement, if dollar figure is less than zero(a loss) then print in red, otherwise print in default color of black -->
<xsl:choose>
<xsl:when test="$anum < 0">
<TD CLASS="red"> $ <xsl:value-of select ="format-number($anum,'###,##0.00')" /> </TD> </xsl:when>
<xsl:otherwise>
<TD> $ <xsl:value-of select ="format-number($anum,'###,##0.00')" /> </TD>
</xsl:otherwise>
</xsl:choose>

</xsl:for-each> <!-- End of for each field loop --> </TR>

</xsl:for-each> <!--End of for each record loop -->
</xsl:template> <!--End of table rows template -->

</xsl:stylesheet>

Home     SQLite Site Index