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