Calculate Sum of Values with XML

Post Reply
Mark Casey
Member
Posts: 24
Joined: Thu Sep 26, 2019 1:47 pm
Location: Leeds, UK

Calculate Sum of Values with XML

Post by Mark Casey »

HI Guys
I'm looking at a way to Calculate the Sum of Values with an XML
XML Below, I need the Sum of all the <No-Up> should come out at 15
Any ideas?

<root>
<record>
<UKG-Product-Code>107033-0-1</UKG-Product-Code>
<Card-Quantity>3000</Card-Quantity>
<Material>BRAMPTONSTUC-280-FSC</Material>
<Prnt>CC 3K 4/1</Prnt>
<No-Up>1</No-Up>
<Print-Qty>3250</Print-Qty>
</record>
<record>
<UKG-Product-Code>117706-0-1</UKG-Product-Code>
<Card-Quantity>4500</Card-Quantity>
<Material>BRAMPTONSTUC-280-FSC</Material>
<Prnt>CC 3K 4/1</Prnt>
<No-Up>2</No-Up>
<Print-Qty>3250</Print-Qty>
</record>
<record>
<UKG-Product-Code>558465-0-1</UKG-Product-Code>
<Card-Quantity>7500</Card-Quantity>
<Material>BRAMPTONSTUC-280-FSC</Material>
<Prnt>CC 3K 4/4</Prnt>
<No-Up>3</No-Up>
<Print-Qty>3250</Print-Qty>
</record>
<record>
<UKG-Product-Code>558468-0-1</UKG-Product-Code>
<Card-Quantity>24000</Card-Quantity>
<Material>BRAMPTONSTUC-280-FSC</Material>
<Prnt>CC 3K 4/4</Prnt>
<No-Up>8</No-Up>
<Print-Qty>3250</Print-Qty>
</record>
<record>
<UKG-Product-Code>567641-0-1</UKG-Product-Code>
<Card-Quantity>3000</Card-Quantity>
<Material>BRAMPTONSTUC-280-FSC</Material>
<Prnt>CC 3K 4/4</Prnt>
<No-Up>1</No-Up>
<Print-Qty>3250</Print-Qty>
<Spot-Colour>PMS 811</Spot-Colour>
</record>
</root>
User avatar
magnussandstrom
Advanced member
Posts: 443
Joined: Thu Jul 30, 2020 6:34 pm
Location: Sweden
Contact:

Re: Calculate Sum of Values with XML

Post by magnussandstrom »

I would do this using XSLT (solved with a quick conversation with chatGPT):

You can calculate the sum of the <No-Up> values in your XML using XSLT. Below is a simple XSLT stylesheet that will do this:

XSLT:

Code: Select all

<?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"/>

    <xsl:template match="/">
        <xsl:variable name="sum">
            <xsl:for-each select="root/record/No-Up">
                <xsl:value-of select="."/>
            </xsl:for-each>
        </xsl:variable>
        <xsl:value-of select="sum(root/record/No-Up)"/>
    </xsl:template>
</xsl:stylesheet>
Explanation:
Match the Root: The template matches the root element.
Variable Declaration: A variable $sum is defined to accumulate the values of <No-Up>, but this variable isn't strictly necessary.

Sum Calculation: The sum() function is used directly on the XPath expression root/record/No-Up to calculate the total sum of all <No-Up> values.

How to Use It:
Save the above XSLT code to a file, e.g., sum_no_up.xsl.
Use an XSLT app (like Saxon or XSLT transform) to apply the transformation to your XML file.

Output:
When you run the transformation, the output will be: 15
r.zegwaard
Member
Posts: 98
Joined: Fri Jul 08, 2011 10:31 am
Location: The Netherlands

Re: Calculate Sum of Values with XML

Post by r.zegwaard »

Hi

If you want to use the metadata, then you can also use an xpath expression to calculate the sum.
Note: the type must be a Integer or Rational

[Metadata.Integer:Dataset="Xml",Model="XML",Path="sum(//No-Up)"]

jRjhbpRHRo.png
jRjhbpRHRo.png (28.22 KiB) Viewed 2137 times

Robert
User avatar
magnussandstrom
Advanced member
Posts: 443
Joined: Thu Jul 30, 2020 6:34 pm
Location: Sweden
Contact:

Re: Calculate Sum of Values with XML

Post by magnussandstrom »

Roberts' solution is clearly the superior choice between the two options. :)
Post Reply