CODEWITHSUNDEEP
xmlxsltsumxslt-1.0xslt-2.0
Ben Alibasic
Ben Alibasic

Reputation: 23

I need to sum certain nodes in xslt based on the index and value of sibling nodes

In the xml structure below I need to use xsl to sum the values of each record type's costs if the record type is "ADD"

<records> 
   ...irrelevant nodes...
   <recordType>NO</recordType>
   <recordType>ADD</recordType>
   <recordType>ADD</recordType>
   ... irrelevant nodes...
   <LdgCost>1</LdgCost>
   <LabCostIn>2</LabCostIn>
   <LabCostOut>3</LabCostOut>
   <LdgCost>4</LdgCost>
   <LabCostIn>5</LabCostIn>
   <LabCostOut>6</LabCostOut>
   <LdgCost>7</LdgCost>
   <LabCostIn>8</LabCostIn>
   <LabCostOut>9</LabCostOut>
   ...irrelevant nodes...
</records>

(the record type's costs are the elements below in the same index as the record type). This means the recordType in the first position that is of type "NO" does not need to be added so its values 

<LdgCost>1</LdgCost>
<LabCostIn>2</LabCostIn>
<LabCostOut>3</LabCostOut>

do not need to be summed. However, the next two record types are "ADD" and therefore I need to sum the values of 

<LdgCost>4</LdgCost>
<LabCostIn>5</LabCostIn>
<LabCostOut>6</LabCostOut>
<LdgCost>7</LdgCost>
<LabCostIn>8</LabCostIn>
<LabCostOut>9</LabCostOut>

and set that as my total. Output would just be a total element 

<total>39</total>

Logically the structure that the above xml structure represents if it were using parent-child nodes is as follows.

<records> 
   <record>
       <recordType>NO</recordType>
       <LdgCost>1</LdgCost>
       <LabCostIn>2</LabCostIn>
       <LabCostOut>3</LabCostOut>
   </record>
   <record>
       <recordType>ADD</recordType>
       <LdgCost>4</LdgCost>
       <LabCostIn>5</LabCostIn>
       <LabCostOut>6</LabCostOut>
   </record>
   <record>
       <recordType>ADD</recordType>
       <LdgCost>7</LdgCost>
       <LabCostIn>8</LabCostIn>
       <LabCostOut>9</LabCostOut>
   </record>
</records>

However instead of using this structure I have to base the parent child relationships based on the index of the sibling elements.

The way I would do this manually is as follows:

  1. Find the first <recordType> element, Check if value is ADD or NO. Since value is NO I skip this one.

  2. Find the next <recordType> element, Check if value is ADD or NO. Since value is ADD then I would need to find the 3 costs related to this second record. These cost elements are guaranteed to exist and be in the index corresponding to their recordType.

  3. Find <LdgCost>[2] element value and add it to sum. (the index is two because we are on the second recordType since we skipped the first one and its costs were ignored)

  4. Find <LdgCostIn>[2] element value and add it to sum.

  5. Find <LdgCostOut>[2] element value and add it to the sum.

  6. Now that we have added the three costs from the second record to the sum and we move on to the third <recordType> element and check its value. Since the value is ADD we find the 3 costs associated to the third record.

  7. Find <LdgCost>[3] element value and add it to sum.(the index is three because we are on the third recordType)

  8. Find <LdgCostIn>[3] element value and add it to sum.

  9. Find <LdgCostOut>[3] element value and add it to the sum.

  10. There are no more <recordType> elements to process so we return the sum of 39.

Upvotes: 2

Views: 1419

Answers (4)

Dimitre Novatchev
Dimitre Novatchev

Reputation: 243509

The wanted sum can be expressed as a single XPath 2.0 expression. Below are two different such expressions:

sum(/*/*[name()=('LdgCost', 'LabCostIn', 'LabCostOut')]
           [for $vPos in ceiling(position() div 3)
             return 
               /*/recordType[$vPos] eq 'ADD']
   )

or:

sum(for $vAddPositions in index-of(/*/recordType, 'ADD')
      return
        /*/*[name()=('LdgCost', 'LabCostIn', 'LabCostOut')]
               [ceiling(position() div 3) = $vAddPositions]
   )

Here is an XSLT 2.0 - based verification (the transformation just outputs the results of evaluating these two expressions):

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="text"/>

  <xsl:template match="/">
    <xsl:sequence select=
    "sum(/*/*[name()=('LdgCost', 'LabCostIn', 'LabCostOut')]
               [for $vPos in ceiling(position() div 3)
                 return 
                   /*/recordType[$vPos] eq 'ADD']
        )
    "/>

    =====================

    <xsl:sequence select=
    "sum(for $vAddPositions in index-of(/*/recordType, 'ADD')
          return
            /*/*[name()=('LdgCost', 'LabCostIn', 'LabCostOut')]
                   [ceiling(position() div 3) = $vAddPositions]
         )
    "/>
   </xsl:template>
</xsl:stylesheet>

When this transformation is applied on the provided XML document:

<records> 
   ...irrelevant nodes...
    <recordType>NO</recordType>
    <recordType>ADD</recordType>
    <recordType>ADD</recordType>
   ... irrelevant nodes...
    <LdgCost>1</LdgCost>
    <LabCostIn>2</LabCostIn>
    <LabCostOut>3</LabCostOut>
    <LdgCost>4</LdgCost>
    <LabCostIn>5</LabCostIn>
    <LabCostOut>6</LabCostOut>
    <LdgCost>7</LdgCost>
    <LabCostIn>8</LabCostIn>
    <LabCostOut>9</LabCostOut>
   ...irrelevant nodes...
</records>

the wanted, correct result is produced:

39

=====================

39

Upvotes: 1

Phil Blackburn
Phil Blackburn

Reputation: 1067

Do this give you what you need:

Using XSLT 1 and MSXML

<xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
    exclude-result-prefixes="msxsl"
    >
  <xsl:output method="xml"/>
  <xsl:template match="/">
      <!-- create a set of node for all the values that need to be added -->
      <xsl:variable name="values">
        <xsl:apply-templates select="records/recordType"/>
      </xsl:variable>
        <!-- at this point $values contains a fragment with groups of
        <LdgCost>value</LdgCost>
        <LabCostIn>value</LabCostIn>
        <LabCostOut>value</LabCostOut>
        -->
        <total>
            <!-- sum up all the values -->
           <xsl:value-of select="sum(msxsl:node-set($values)/*)"/>
        </total>
  </xsl:template>

  <xsl:template match="recordType[.='ADD']">
      <!-- the index for the ADD recordType -->
      <xsl:variable name="index" select="position()"/>
      <!-- select costs using index -->
      <xsl:copy-of select="/records/LdgCost[$index]"/>
      <xsl:copy-of select="/records/LabCostIn[$index]"/>
      <xsl:copy-of select="/records/LabCostOut[$index]"/>
  </xsl:template>
</xsl:stylesheet>

I'm using a node-set function here and that will be different for each xslt processor.

Using you data, it returns a single node:

<total>39</total>

Upvotes: 0

michael.hor257k
michael.hor257k

Reputation: 117043

--- edited in response to clarifications ---

XSLT 2.0

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

<xsl:template match="/records">
    <xsl:variable name="i" select="index-of(recordType, 'ADD')" />
    <xsl:variable name="costs" select="LabCost | LabCostIn | LabCostOut" />
    <total>
        <xsl:copy-of select="sum($costs[(position() - 1) idiv 3 + 1 = $i])"/>
    </total>
</xsl:template>

</xsl:stylesheet>

Upvotes: 1

Martin Honnen
Martin Honnen

Reputation: 167641

If you know the element names you can use

<xsl:template match="/records">
    <xsl:variable name="positions" select="index-of(recordType, 'ADD')"/>
    <total>
        <xsl:value-of select="sum(LdgCost[position() = $positions]
            | LabCostIn[position() = $positions]
            | LabCostOut[position() = $positions])"/>
    </total>
</xsl:template>

Upvotes: 2

Related Questions