Sum only numeric columns with XSL

Question

I have some XML like this:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="test.xsl"?>
<root>
    <line>
        <ResourceName>Ren</ResourceName>
        <Amount>20</Amount>
        <OtherAmount>5</OtherAmount>
        <SomeText>Nopls</SomeText>
    </line>
    <line>
        <ResourceName>Stimpy</ResourceName>
        <Amount>30</Amount>
        <OtherAmount>10</OtherAmount>
        <SomeText>Do_not_sum</SomeText>
    </line>
</root>

but importantly the number of 'columns' below line node could more or less and any name (dynamic varying).

I want to generate a HTML table with the node names as a header and a total for any numeric columns in the footer.

So far I have an XSLT as follows:

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

<xsl:template match="root">
<html>
<body>
    <table border="1">
        <thead>
            <xsl:apply-templates select="line[1]" mode="thead"/>
        </thead>
        <tbody>
            <xsl:apply-templates select="line" />
        </tbody>
        <tfoot>
            <tr>
                <td>Totals:</td>
                <td>
                    <xsl:variable name="amount1Lines" select="line/Amount"/>
                    <xsl:value-of select="format-number(sum($amount1Lines), '0.00')" />
                </td>
                <td>
                </td>
                <td>
                </td>
                <td>
                </td>
            </tr>
        </tfoot>
    </table>
</body>
</html>

</xsl:template>

<xsl:template match="line" mode="thead">
    <tr>
        <xsl:apply-templates select="*" mode="thead"/>
    </tr>
</xsl:template>

<xsl:template match="*" mode="thead">
    <th>
        <xsl:value-of select="local-name()" />
    </th>
</xsl:template>

<xsl:template match="line">
    <tr>
        <xsl:apply-templates select="*" />
    </tr>
</xsl:template>

<xsl:template match="line/*">
    <td>
        <xsl:value-of select="." />
    </td>
</xsl:template>

</xsl:stylesheet>

but obviously the footer section is hardcoded for each column.

Can anyone determine some xsl that will sum only numeric columns and leave other columns blank in the footer. ie. In the example it would sum 'Amount' and 'OtherAmount' but not resourcename or sometext columns.

do you want to sum ALL numeric nodes? e.g., from the above sample code, you would expect a value of 65? — Joel M. Lamsen
No. I want a table where each column (eg. Amount) has a sum in the footer. So Amount has 50 in the footer and OtherAmount has 15 in the footer. — user3492032

StuartLC StuartLC · Accepted Answer · 2014-04-03T04:29:03

By repeating the same pattern you have for thead for tfoot, and then using this trick from Dimitre for determining whether a sample column is numeric, you can total the columns as follows:

<tfoot><tr>
    <xsl:apply-templates select="line[1]/*" mode="tfoot"/>
</tr></tfoot>

<xsl:template match="*" mode="tfoot">
    <td>
        <xsl:variable name="columnName" select="local-name()"></xsl:variable>
        <xsl:if test="number(//line[1]/*[local-name()=$columnName]) = 
                      number(//line[1]/*[local-name()=$columnName])" >
            <xsl:value-of select="sum(//line/*[local-name() = $columnName])" />
        </xsl:if>
    </td>
</xsl:template>

Edit The existing line <tr> template will do fine for the footer as well (Thanks @anon editor).

Re: 2 Tables

Given the xml:

<xml>
    <root>
        <line>...</line>
        ...
    </root>
    <root>
        ..

You can change the summation to restrict to just the current root data by walking up the ancestor axis to find lines for just 'this' root:

<xsl:if test="number(ancestor::root/line[1]/*[local-name()=$nodeName]) 
              = number(ancestor::root/line[1]/*[local-name()=$nodeName])" >
    <xsl:value-of select="sum(ancestor::root/line/*[local-name() = $nodeName])" />
</xsl:if>

Sum only numeric columns with XSL

1 Answers