CODEWITHSUNDEEP
xmlxsltsum
Trevor Smith
Trevor Smith

Reputation: 31

(XSLT) How to sum values based on sibling node

I have an XSLT problem. I would like to sum all subtotal values when the invoice number does NOT start with the letter 'M'. The problem I have is that I use the SUM function, but I don't know how to give it conditions.

I've used IF constructs in the past, but they were used to generate a string like "456 + 415" and I need a single summed number.

Here is the XML I want to translate:

<PriceSheets>
    <PriceSheet>
        <SubTotal>456</SubTotal>
        <Settlement>
            <InvoiceNumber>M1234567890</InvoiceNumber>
        </Settlement>
    </PriceSheet>
        <SubTotal>415</SubTotal>
        <Settlement>
           <InvoiceNumber>1234567891</InvoiceNumber>
        </Settlement>
    <PriceSheet>
    </PriceSheet>
</PriceSheets>

In this case I expect to see "415". Any help would be greatly appreciated!

Thanks

PS: I used to use 

<xsl:value-of select="sum(PriceSheets/PriceSheet/SubTotal/text())" />

to sum all the values, but the problem is that I need to look into the Settlement/InvoiceNumber node before I sum the value.

Upvotes: 1

Views: 760

Answers (3)

Trevor Smith
Trevor Smith

Reputation: 31

Thank you all for your input! I used a your examples to get working code!

sum(PriceSheets/PriceSheet[substring(Settlement/InvoiceNumber/text(), 1, 1) != 'M']/SubTotal/text())

I also experimented with something else that also worked:

<xsl:value-of select="sum(PriceSheets/PriceSheet[not(starts-with(Settlement/InvoiceNumber, 'M'))]/SubTotal/text())" />

Both of these are working for me! Thank you everyone :)

Upvotes: 2

Dan Field
Dan Field

Reputation: 21661

You want to select all subtotals where the sibling settlement node's invoicenumber doesn't start with 'M': 

<xsl:value-of select="sum(//SubTotal[substring(../Settlement/InvoiceNumber, 1, 1) != 'M'])" />

Upvotes: 0

samjudson
samjudson

Reputation: 56893

Your XML looks invalid, but assuming each PriceSheet has a Settlment element, and a SubTotal element then you'd want this:

<xsl:value-of 
  select="sum(PriceSheets/PriceSheet[substring(Settlement/InvoiceNumber, 1, 1)<>'M']/SubTotal/text())" />

Upvotes: 0

Related Questions