XSLT: Sum multiple items based on attributes-CodePudding

I apologize in advance for this one, it's a little complicated and I don't know all the proper terms... I am still learning XSL. I ran into an problem that I can't figure out:

How do I sum data across multiple areas based on a unique attribute?

The data set I have is pretty large, so I'm going to try to paraphrase it with some psuedocode for clarity:

<InRoads ....>
  <GeometryProject....>
    <HorizontalAlignment name="40000" TaxID="123" area="100" Type="FEE" ExArea="2000">
       ...
    </HorizontalAlignment>
    <HorizontalAlignment name="40001" TaxID="123" area="100" Type="TCE" ExArea="2000">
       ...
    </HorizontalAlignment>
    <HorizontalAlignment name="40002" TaxID="123" area="100" Type="PE" ExArea="2000">
       ...
    </HorizontalAlignment>
    <HorizontalAlignment name="40003" TaxID="456" area="100" Type="FEE" ExArea="5000">
       ...
    </HorizontalAlignment>
    <HorizontalAlignment name="40004" TaxID="456" area="100" Type="FEE" ExArea="5000">
       ...
    </HorizontalAlignment>
    <HorizontalAlignment name="40005" TaxID="456" area="100" Type="TCE" ExArea="5000">
       ...
    </HorizontalAlignment>
    <HorizontalAlignment name="40006" TaxID="789" area="100" Type="FEE" ExArea="8000">
       ...
    </HorizontalAlignment>
    <HorizontalAlignment name="40007" TaxID="789" area="100" Type="FEE" ExArea="8000">
       ...
    </HorizontalAlignment>
    <HorizontalAlignment name="40008" TaxID="789" area="100" Type="PE" ExArea="8000">
       ...
    </HorizontalAlignment>
  </GeometryProject>
</InRoads>

I want to sum up the @area for each @TaxID, as long as the @Type is FEE, and then subtract from @ExArea.

So, TaxID=789 for example: 8000 - (100 100) = 7800

So from the above psuedocode, the expected output would be

TaxID 123 remaining area = 1900
TaxID 456 remaining area = 4800
TaxID 789 remaining area = 7800

I was able to figure out how to subtract @area from @ExArea if Type='FEE', but I cannot figure out how to sum based on the TaxID.

Any help would be greatly appreciated.

CodePudding user response：

An XSLT-2.0 solution is straightforward: First group, then construct the output line.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/InRoads/GeometryProject">
    <xsl:for-each-group select="HorizontalAlignment" group-by="@TaxID">
        <xsl:value-of select="'TaxID ',current-grouping-key(), ' remaining area = ', current-group()[1]/@ExArea - sum(current-group()[@Type='FEE']/@area)" />
        <xsl:text>&#xa;</xsl:text>   <!-- New line -->
    </xsl:for-each-group>
</xsl:template>

</xsl:stylesheet>

Its output is (as expected):

TaxID 123 remaining area = 1900
TaxID 456 remaining area = 4800
TaxID 789 remaining area = 7800

With XSLT-1.0, it's a little bit more complicated, because you have to use Muenchian Grouping. You can achieve this using an xsl:key with an xsl:for-each:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:key name="TAXIDS" match="HorizontalAlignment" use="@TaxID" />
    
    <xsl:template match="/InRoads/GeometryProject">
        <xsl:for-each select="HorizontalAlignment[generate-id()=generate-id(key('TAXIDS',@TaxID)[1])]">
            <xsl:value-of select="concat('TaxID ',@TaxID, ' remaining area = ', (@ExArea - sum(key('TAXIDS',@TaxID)[@Type='FEE']/@area)))" />
            <xsl:text>&#xa;</xsl:text>   <!-- New line -->
        </xsl:for-each>
    </xsl:template>
    
</xsl:stylesheet>

The output is the same. Googling or looking on SO for "Muenchian Grouping" will give you a lot of explanations which are far more descriptive than what I could give you in this answer.