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, Apr 03 '14 at 04:06
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, Apr 03 '14 at 04:11

score 1 · Accepted Answer · edited May 23 '17 at 12:05

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>

If I have another block with "" how can I avoid picking up lines from the other block and still have a generic template for the footer sum? ie. Only total children of "root" for root lines, and children of "root2" for root2 lines? — user3492032, Apr 03 '14 at 23:50
Updated - although you might have quicker response if you ask a new question next time :-) — StuartLC, Apr 04 '14 at 05:03

Sum only numeric columns with XSL

1 Answers1