How to iterate over unique records in XSLT 1.0?

Question

I have an xml input like this :

<DepartmentData>
<Department>
    <Employees>
        <Employee>
            <Id>123</Id>
            <Name>John</Name>
            <Incentive>1000</Incentive>
        </Employee>
        <Employee>
            <Id>789</Id>
            <Name>Mmark</Name>
            <Incentive>5000</Incentive>
        </Employee>
        <Employee>
            <Id>123</Id>
            <Name>John</Name>
            <Incentive>4000</Incentive>
        </Employee>
    </Employees>
</Department>
<Department>
 <Employees>
    <Employee>
        <Id>674</Id>
        <Name>John</Name>
        <Incentive>1000</Incentive>
    </Employee>
    <Employee>
        <Id>334</Id>
        <Name>Mmark</Name>
        <Incentive>5000</Incentive>
    </Employee>
    <Employee>
        <Id>334</Id>
        <Name>Mmark</Name>
        <Incentive>4000</Incentive>
    </Employee>
 </Employees>
</Department>
<Department>
    <Employees>
        <Employee>
            <Id>009</Id>
            <Name>John</Name>
            <Incentive>1000</Incentive>
        </Employee>
        <Employee>
            <Id>887</Id>
            <Name>Mmark</Name>
            <Incentive>5000</Incentive>
        </Employee>
        <Employee>
            <Id>678</Id>
            <Name>John</Name>
            <Incentive>4000</Incentive>
        </Employee>
    </Employees>
 </Department>
</DepartmentData>

There are multiple Departments and each Employees record contains multiple Employee details.

I need to loop through each department and then each employee group the employee with same emp id to sum their incentives.

Expected output :

<root>
<Emps>
    <Emp>
        <id>123</id>
        <incentive>5000</incentive>
    </Emp>
    <Emp>
        <id>789</id>
        <incentive>5000</incentive>
    </Emp>
</Emps>
<Emps>
    <Emp>
        <id>674</id>
        <incentive>1000</incentive>
    </Emp>
    <Emp>
        <id>334</id>
        <incentive>9000</incentive>
    </Emp>
  </Emps>
<Emps>
    <Emp>
        <id>009</id>
        <incentive>1000</incentive>
    </Emp>
    <Emp>
        <id>887</id>
        <incentive>5000</incentive>
    </Emp>
    <Emp>
        <id>678</id>
        <incentive>4000</incentive>
    </Emp>
  </Emps>

</root>

Could you please help how can I achieve this task ?

Tried checking multiple posts.

Answer 1

I expect that probably the complexity that's tripping you up is that a given Employee can appear in multiple Department elements, and you want to group together all the Employee elements with a given Id per Department. So this makes it a little more difficult than most 'grouping' problems in XSLT 1.0, but in fact the same techniques that are normally used can work in this case as well.

For a simpler grouping case, you would define an xsl:key to match an Employee element and use its Id child element as the key, so that later you could use it to retrieve the entire group of Employee elements with a given Id. For example:

<xsl:key name="employee-by-id" match="Employee" use="Id"/>

But in this case, you need to group the Employee elements not just by their Id, but also by their Department ancestor element. So for example you could define a key like so:

<xsl:key name="employee-by-department-and-id" match="Employee" use="
   concat(
      generate-id(ancestor::Department), 
      Id
   )
"/>

NB the generate-id() function is a way to uniquely identify any element. It's often used with xsl:key/@use.

To iterate over all the unique values of Employee/Id within a given Department, you can select all the Employee elements within each Department, and filter them with a predicate that checks that the current Employee is equal to the first Employee with that Department identifier and Id. That gives you the list of distinct Employee identifiers.

The final piece of the puzzle is to call the key function for each of those employee identifiers and their corresponding department identifiers, giving you a group of Employee elements, which you can then summarize in whatever way you need.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

  <xsl:output method="xml" indent="true"/>
  
  <xsl:key name="employee-by-department-and-id" match="Employee" use="
    concat(
      generate-id(ancestor::Department), 
      Id
    )
  "/>

  <xsl:template match="/">
    <root>
      <xsl:for-each select="//Department">
         <xsl:variable name="department-identifier" select="generate-id(.)"/>
         <xsl:variable name="unique-employee-identifiers" select="
            descendant::Employee[
               generate-id(.) = 
               generate-id(
                  key(
                     'employee-by-department-and-id', 
                     concat($department-identifier, Id)
                  )[1]
               )
            ]/Id
         "/>
         <Emps>
            <xsl:for-each select="$unique-employee-identifiers">
               <xsl:variable name="employee-group" select="
                  key(
                     'employee-by-department-and-id', 
                     concat($department-identifier, .)
                  )
               "/>
               <Emp>
                  <id><xsl:value-of select="$employee-group[1]/Id"/></id>
                  <incentive><xsl:value-of select="sum($employee-group/Incentive)"/></incentive>
               </Emp>
            </xsl:for-each>
         </Emps>
      </xsl:for-each>
    </root>
  </xsl:template>
</xsl:stylesheet>

Output as expected:

<root>
   <Emps>
      <Emp>
         <id>123</id>
         <incentive>5000</incentive>
      </Emp>
      <Emp>
         <id>789</id>
         <incentive>5000</incentive>
      </Emp>
   </Emps>
   <Emps>
      <Emp>
         <id>674</id>
         <incentive>1000</incentive>
      </Emp>
      <Emp>
         <id>334</id>
         <incentive>9000</incentive>
      </Emp>
   </Emps>
   <Emps>
      <Emp>
         <id>009</id>
         <incentive>1000</incentive>
      </Emp>
      <Emp>
         <id>887</id>
         <incentive>5000</incentive>
      </Emp>
      <Emp>
         <id>678</id>
         <incentive>4000</incentive>
      </Emp>
   </Emps>
</root>