XSLT: Sum multiple items based on attributes

Question

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.

Which XSLT version can you use? Can you use XSLT-2.0? Because this is some kind of grouping question. — zx485, Sep 22 '21 at 18:42
In all your questions regarding XSLT, please state which version of XSLT your processor supports. This is first and foremost a [grouping](https://stackoverflow.com/tags/xslt-grouping/info) question and grouping methods are different for XSLT 1.0 or 2.0. Also in XSLT 2.0 you can sum the results of a calculation directly, while in XSLT 1.0 it's a two-step process. — michael.hor257k, Sep 22 '21 at 18:45
I'll see if they support 2.0, but I am gonna assume i'm stuck with 1.1 — Dave, Sep 22 '21 at 18:49
Your stylesheet does not determine the version you can use. Your processor does - see: https://stackoverflow.com/a/25245033/3016153 — michael.hor257k, Sep 22 '21 at 19:22
There's no such thing as version 1.1. There was a draft of a version 1.1 in 2001, which was withdrawn within a year, but somehow people still copy-and-paste version="1.1" into their stylesheets. All it tells you is that the code was written by someone who's not exactly an XSLT expert. — Michael Kay, Sep 23 '21 at 08:00
@MichaelKay If a stylesheet relies on the processor being able to process variable content as a node-set , then it is proper to mark its version as 1.1. — michael.hor257k, Sep 23 '21 at 13:34
@michael.hor257k Well, I guess if you're doing things that rely on non-conformant behaviour then I guess you can do anything you like... — Michael Kay, Sep 23 '21 at 14:53

zx485 · Accepted Answer · 2021-09-22T19:35:05.603

2

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.

edited Sep 22 '21 at 19:35

answered Sep 22 '21 at 19:13

zx485

28,498
28
50
59

Thank you so much! I will test this tomorrow and start reading about the Muenchian Grouping. – Dave Sep 22 '21 at 19:17
1

`current()[1]` ???? – michael.hor257k Sep 22 '21 at 19:21
1

The outer parentheses are also redundant. I also don't see the point of concatenating a string just before writing it to the output, but that's another story. – michael.hor257k Sep 22 '21 at 19:27
1

@michael.hor257k: I also removed the `fn:concat()`. In the XSLT-2.0 it was a backwards compatible rudiment. – zx485 Sep 22 '21 at 19:37
1

I'm afraid you misunderstood me, because you still use `concat()`. I would do it this way: https://xsltfiddle.liberty-development.net/gVAkJ57 – michael.hor257k Sep 22 '21 at 19:41
1

@michael.hor257k: OK, now I get what you were trying to convey. And IMHO this is merely a matter of taste/style. There may be pros and cons, but I don't think that the difference is crucial. But thank you for being so patient to explain. – zx485 Sep 22 '21 at 19:52
1

I believe it's also a matter of removing one step of redundant processing. But I have no way to prove it. – michael.hor257k Sep 22 '21 at 19:55
Ok, this did it! I had a little trouble getting it into the rest of the style sheet, but it worked! Thankyou all so much for the help. I have so much to learn. I had to use the 1.0 approach as I could not get 2.0 to work. – Dave Sep 23 '21 at 13:20

XSLT: Sum multiple items based on attributes

1 Answers1