CODEWITHSUNDEEP
xmlxsltxpathxslt-1.0
Zirous Tom
Zirous Tom

Reputation: 175

Is it possible to Sum this data with XSLT?

Is it possible to Sum this data with XSLT? I have the following dataset

<?xml version="1.0" encoding="utf-8"?>
<data>
    <recordType>EXP</recordType>
      <AppContribAmt>0.0</AppContribAmt>
      <LdgCost>658.82</LdgCost>
      <LabCostIn>0.0</LabCostIn>
      <LabCostOut>0.0</LabCostOut>
      <ServiceCost>0.0</ServiceCost>
      <MatCostIn>0.0</MatCostIn>
      <MatCostOut>0.0</MatCostOut>
      <ToolCostIn>0.0</ToolCostIn>
      <ToolCostOut>0.0</ToolCostOut>
      <recordType>ADD</recordType>
      <AppContribAmt>0.0</AppContribAmt>
      <LdgCost>932.5</LdgCost>
      <LabCostIn>104.64</LabCostIn>
      <LabCostOut>0.0</LabCostOut>
      <ServiceCost>0.0</ServiceCost>
      <MatCostIn>260.36</MatCostIn>
      <MatCostOut>0.0</MatCostOut>
      <ToolCostIn>0.0</ToolCostIn>
      <ToolCostOut>0.0</ToolCostOut>
      <recordType>ADD</recordType>
      <AppContribAmt>0.0</AppContribAmt>
      <LdgCost>932.5</LdgCost>
      <LabCostIn>104.64</LabCostIn>
      <LabCostOut>0.0</LabCostOut>
      <ServiceCost>0.0</ServiceCost>
      <MatCostIn>260.36</MatCostIn>
      <MatCostOut>0.0</MatCostOut>
      <ToolCostIn>0.0</ToolCostIn>
      <ToolCostOut>0.0</ToolCostOut>
      <recordType>ADD</recordType>
      <AppContribAmt>0.0</AppContribAmt>
      <LdgCost>932.5</LdgCost>
      <LabCostIn>104.64</LabCostIn>
      <LabCostOut>0.0</LabCostOut>
      <ServiceCost>0.0</ServiceCost>
      <MatCostIn>260.36</MatCostIn>
      <MatCostOut>0.0</MatCostOut>
      <ToolCostIn>0.0</ToolCostIn>
      <ToolCostOut>0.0</ToolCostOut>
      <recordType>EXP</recordType>
      <AppContribAmt>0.0</AppContribAmt>
      <LdgCost>0.0</LdgCost>
      <LabCostIn>322.95</LabCostIn>
      <LabCostOut>0.0</LabCostOut>
      <ServiceCost>0.0</ServiceCost>
      <MatCostIn>0.0</MatCostIn>
      <MatCostOut>0.0</MatCostOut>
      <ToolCostIn>0.0</ToolCostIn>
      <ToolCostOut>0.0</ToolCostOut>
</data>

I am trying to sum the values under recordType='EXP' to one total and the values under recordType='ADD' to another total. Is this possible or do I need to change the XML format to something like this?

<?xml version="1.0" encoding="utf-8"?>
<data>
    <recordType value='EXP'>
      <AppContribAmt>0.0</AppContribAmt>
      <LdgCost>658.82</LdgCost>
      <LabCostIn>0.0</LabCostIn>
      <LabCostOut>0.0</LabCostOut>
      <ServiceCost>0.0</ServiceCost>
      <MatCostIn>0.0</MatCostIn>
      <MatCostOut>0.0</MatCostOut>
      <ToolCostIn>0.0</ToolCostIn>
      <ToolCostOut>0.0</ToolCostOut>
</recordType>
<recordType value='ADD'>
      <AppContribAmt>0.0</AppContribAmt>
      <LdgCost>932.5</LdgCost>
      <LabCostIn>104.64</LabCostIn>
      <LabCostOut>0.0</LabCostOut>
      <ServiceCost>0.0</ServiceCost>
      <MatCostIn>260.36</MatCostIn>
      <MatCostOut>0.0</MatCostOut>
      <ToolCostIn>0.0</ToolCostIn>
      <ToolCostOut>0.0</ToolCostOut>
</recordType>
</data>

Upvotes: 1

Views: 68

Answers (2)

Daniel Haley
Daniel Haley

Reputation: 52878

Here's another XSLT 1.0 option that is a little bit more complicated, but should be* much more efficient if you have a larger data set.

*I tested with Saxon-HE 9.6 and duplicated your data so the file was 10,353 lines and this stylesheet ran in about 150ms compared to about 1500ms with the other answer (based on the output of the "-t" switch). Results may vary with actual data and different processors, so you might want to test.

XSLT 1.0

<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="recordTypes" match="recordType" use="."/>
  <xsl:key name="byRecordType" match="*[not(self::recordType)]" 
    use="preceding-sibling::recordType[1]"/>

  <xsl:template match="/*">
    <results>
      <xsl:for-each select="recordType[count(.|key('recordTypes',.)[1])=1]">
        <xsl:apply-templates select="."/>
      </xsl:for-each>
    </results>
  </xsl:template>

  <xsl:template match="recordType">
    <sum type="{.}">
      <xsl:value-of select="sum(key('byRecordType',.))"/>
    </sum>
  </xsl:template>

</xsl:stylesheet>

Output

<results>
   <sum type="EXP">981.77</sum>
   <sum type="ADD">3892.5</sum>
</results

Upvotes: 1

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56182

I would use something like this for the first XML:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes"/>

  <xsl:key name="k" match="recordType" use="."/>

  <xsl:template match="recordType[generate-id() = generate-id(key('k', .))]">
    <total name="{.}">
      <xsl:value-of select="sum(key('k', .)
                    /following-sibling::*[not(self::recordType)][preceding-sibling::recordType[1] = current()])"/>
    </total>
  </xsl:template>

  <xsl:template match="text()"/>
</xsl:stylesheet>

Output:

<total name="EXP">981.77</total>
<total name="ADD">3892.5</total>

Upvotes: 1

Related Questions