XML Sum Function

Question

I am trying to create a sum based on equal IDs in an XSLT file and output it in an extra attribute. Maybe one of you can help me?

The followed XML is an output:

<?xml version="1.0" encoding="utf-8"?>
<document>
  <fields>
    <field id="CrdCountry" type="STGString">DE</field>
    <field id="CrdZipCode" type="STGString">57080</field>
    <field id="CrdCity" type="STGString">Siegen</field>
    <field id="DocOrdNo" type="STGString" />
    <field id="DocDeliveryNo" type="STGString">442915</field>
    <field id="ClassMa" type="STGString" />
  </fields>
  <tables>
    <table id="TabVat">
      <tablerow index="0">
        <tablecell colname="TabNetAmount" type="STGDecimal">100.00</tablecell>
        <tablecell colname="TabVatAmount" type="STGDecimal">7.00</tablecell>
        <tablecell colname="TabVatRate" type="STGDecimal">7</tablecell>
        <tablecell colname="TabVatCode" type="STGString">7</tablecell>
      </tablerow>
    <tablerow index="1">
        <tablecell colname="TabNetAmount" type="STGDecimal">100.0</tablecell>
        <tablecell colname="TabVatAmount" type="STGDecimal">7.00</tablecell>
        <tablecell colname="TabVatRate" type="STGDecimal">7</tablecell>
        <tablecell colname="TabVatCode" type="STGString">7</tablecell>
      </tablerow>
        <tablerow index="2">
        <tablecell colname="TabNetAmount" type="STGDecimal">200.0</tablecell>
        <tablecell colname="TabVatAmount" type="STGDecimal">14.00</tablecell>
        <tablecell colname="TabVatRate" type="STGDecimal">7</tablecell>
        <tablecell colname="TabVatCode" type="STGString">7</tablecell>
      </tablerow>
    <tablerow index="3">
        <tablecell colname="TabNetAmount" type="STGDecimal">100.0</tablecell>
        <tablecell colname="TabVatAmount" type="STGDecimal">19.00</tablecell>
        <tablecell colname="TabVatRate" type="STGDecimal">19</tablecell>
        <tablecell colname="TabVatCode" type="STGString">19</tablecell>
      </tablerow>
      <tablerow index="4">
        <tablecell colname="TabNetAmount" type="STGDecimal">100.0</tablecell>
        <tablecell colname="TabVatAmount" type="STGDecimal">19.00</tablecell>
        <tablecell colname="TabVatRate" type="STGDecimal">19</tablecell>
        <tablecell colname="TabVatCode" type="STGString">19</tablecell>
      </tablerow>
    </table>
    
    <table id="TabOrder">
      <tablerow index="0">
        <tablecell colname="TabOrder1" type="STGDecimal"></tablecell>
        <tablecell colname="TabOrder2" type="STGDecimal"></tablecell>
        <tablecell colname="TabOrder3" type="STGDecimal"></tablecell>
      </tablerow>
     </table>
  </tables>
  </document>

And i want to sum only the table id "TabVAT". My goal is to sum the colnames "TabNetAmount" and "TabVatAmount" grouped by the "TabVatCode". The sum result should be placed in the table id "TabSum" So in this case the result have to look like:

<?xml version="1.0" encoding="utf-8"?>
<document>
  <fields>
    <field id="CrdCountry" type="STGString">DE</field>
    <field id="CrdZipCode" type="STGString">57080</field>
    <field id="CrdCity" type="STGString">Siegen</field>
    <field id="DocOrdNo" type="STGString" />
    <field id="DocDeliveryNo" type="STGString">442915</field>
    <field id="ClassMa" type="STGString" />
  </fields>
  <tables>
    <table id="TabSum">
      <tablerow index="0">
        <tablecell colname="TabNetAmount" type="STGDecimal">400.00</tablecell>
        <tablecell colname="TabVatAmount" type="STGDecimal">28.00</tablecell>
        <tablecell colname="TabVatRate" type="STGDecimal">7</tablecell>
        <tablecell colname="TabVatCode" type="STGString">7</tablecell>
      </tablerow>
    <tablerow index="1">
        <tablecell colname="TabNetAmount" type="STGDecimal">200.0</tablecell>
        <tablecell colname="TabVatAmount" type="STGDecimal">38.00</tablecell>
        <tablecell colname="TabVatRate" type="STGDecimal">19</tablecell>
        <tablecell colname="TabVatCode" type="STGString">19</tablecell>
      </tablerow>
     </table>
    
    <table id="TabOrder">
      <tablerow index="0">
        <tablecell colname="TabOrder1" type="STGDecimal"></tablecell>
        <tablecell colname="TabOrder2" type="STGDecimal"></tablecell>
        <tablecell colname="TabOrder3" type="STGDecimal"></tablecell>
      </tablerow>
     </table>
  </tables>
  </document>

I've an export xslt looks like:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="@* | node()">
      <xsl:copy>
            <xsl:apply-templates select="@* | node()"/>
        </xsl:copy>
    </xsl:template>
<xsl:template match="table">  
    <xsl:value-of select="TabVat/Tabvatcode" />
    <xsl:for-each select="*">
    <!--<xsl:for-each select="tablerow">-->
    Addiere: <xsl:value-of select="TabNetAmount" /><br />
    </xsl:for-each>
    Summe: <xsl:value-of select='sum(*)'/>
</xsl:template>
  
</xsl:stylesheet>

But with all my trying to find a solution, i'm confusing and not solving the problem. Can you please help me and give me a solution?

Thanks a lot

Why does results have index 0 (sum of index 0,1,3,4) and index 1 (index 2). — jdweng
– jdweng, Commented Jul 14, 2023 at 12:03
Your question is not about summing, but about grouping. If you're limited to XSLT 1.0, then start here: jenitennison.com/xslt/grouping/muenchian.html. — michael.hor257k
– michael.hor257k, Commented Jul 14, 2023 at 13:30

Daniel Haley · Accepted Answer · 2023-07-14 16:46:11Z

Like mentioned in the comments, this is a grouping question. Grouping in XSLT 1.0 is done using the Muenchian method. Review that and try to come up with your own solution. That's usually the best way to learn it.

If you get stuck, here's an example...

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:key name="row_by_code" match="tablerow" use="tablecell[@colname='TabVatCode']"/>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="table[@id='TabVat']">
        <xsl:copy>
            <xsl:apply-templates select="@*"/>
            <xsl:for-each select="tablerow[count(.|key('row_by_code',tablecell[@colname='TabVatCode'])[1])=1]">
                <xsl:sort select="tablecell[@colname='TabVatCode']" order="ascending" data-type="number"/>
                <tablerow index="{position()-1}">
                    <xsl:apply-templates select="node()"/>
                </tablerow>
            </xsl:for-each>
        </xsl:copy>
    </xsl:template>
    
    <xsl:template match="tablecell[@colname='TabNetAmount' or @colname='TabVatAmount']/text()">
        <xsl:call-template name="sum_amount">
            <xsl:with-param name="amount_type" select="../@colname"/>
        </xsl:call-template>
    </xsl:template>
    
    <xsl:template name="sum_amount">
        <xsl:param name="amount_type"/>
        <xsl:value-of select="format-number(sum(key('row_by_code',../../tablecell[@colname='TabVatCode'])/tablecell[@colname=$amount_type]),'0.00')"/>
    </xsl:template>
    
</xsl:stylesheet>

Fiddle: http://xsltfiddle.liberty-development.net/3Nzb5k2

Thanks for your replies and the information. I will looking for the muenchian grouping. Thanks for the link, i will read it. And thanks for the code, it helps me a lot. Great, thanks :)

Collectives™ on Stack Overflow

XML Sum Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related