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.
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>