Calculating a Sum of Sums (Final Version)

MSXML 5.0 SDK

Microsoft XML Core Services (MSXML) 5.0 for Microsoft Office - XSLT Developer's Guide

Calculating a Sum of Sums (Final Version)

To correct the failed attempt in the previous topic, wrap the reference to the $ext_price variable in the msxsl:node-set() function call, as follows:

        <tr>
            <th colspan="3">Total, All Items</th>
            <td align="right">
                <xsl:value-of select="format-number(sum(msxsl:node-set($ext_price)/accum), '#,##0.00')"/>
            </td>
        </tr>

You also need to declare the msxsl: namespace prefix:

<xsl:stylesheet version="1.0"
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:msxsl="urn:schemas-microsoft-com:xslt">

Now the table contains the original data and the total of the extended prices. The finished example is shown below.

For more information about the sum() function, see Using Numeric Functions to Perform Math Operations in XPath.

Example

XML File (invoice.xml)

<?xml version='1.0'?>
<?xml-stylesheet type="text/xsl" href="invtot.xsl" ?>
<invoice>
    <item item_num="AX43598">
        <quantity>29</quantity>
        <unit_price>2.00</unit_price>
    </item>
    <item item_num="FH29782">
        <quantity>140</quantity>
        <unit_price>6.50</unit_price>
    </item>
</invoice>

XSLT File (invtot.xsl)

<?xml version='1.0'?>
<xsl:stylesheet version="1.0"
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:msxsl="urn:schemas-microsoft-com:xslt">

<xsl:variable name="ext_price">
    <xsl:for-each select="invoice/item">
        <accum><xsl:value-of select="quantity * unit_price"/></accum>
    </xsl:for-each>
</xsl:variable>

<xsl:template match="/">
    <table border="1" cellpadding="2" cellspacing="0">
        <tr>
            <th>Item</th>
            <th>Quantity</th>
            <th>Unit Price</th>
            <th>Extended Price</th>
        </tr>
        <xsl:for-each select="invoice/item">
            <tr>
                <td>
                    <xsl:value-of select="@item_num"/>
                </td>
                <td align="right">
                    <xsl:value-of select="quantity"/></td>
                <td align="right">
                    <xsl:value-of select="format-number(unit_price, '#,##0.00')"/>
                </td>
                <td align="right">
                    <xsl:value-of select="format-number(quantity * unit_price, '#,##0.00')"/>
                </td>
            </tr>
        </xsl:for-each>
        <tr>
            <th colspan="3">Total, All Items</th>
            <td align="right">
                <xsl:value-of select="format-number(sum(msxsl:node-set($ext_price)/accum), '#,##0.00')"/>
            </td>
        </tr>
    </table>
</xsl:template>

</xsl:stylesheet>

Formatted Output