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


<?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 -->

<Category>Gross Sales</Category>


<Category>Net Profit</Category>


Text of 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"

<xsl:template match="/">
<meta http-equiv="Content-Type" content="utf-8" />

<!--Inline Style Sheet -->
<STYLE type="text/css">
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}




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

<HR />


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

<!--First row of table -->

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

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

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


<xsl:template match="/*/*" > <!--Match each field or column name element tag. -->
<!--Loop for each record or row -->
<xsl:for-each select="*[position()=1]">
<!--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:when test="$anum < 0">
<TD CLASS="red"> $ <xsl:value-of select ="format-number($anum,'###,##0.00')" /> </TD> </xsl:when>
<TD> $ <xsl:value-of select ="format-number($anum,'###,##0.00')" /> </TD>

</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 -->


Home     SQLite Site Index