'I need to remove a node from an XML based on a Condition using Group by

I have the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<root>


<EMPLOYEE_ASSIGNMENT>
   <REFRESH_DATE>2022-03-10 10:55:35.000</REFRESH_DATE>
   <PERSON_ID>11189</PERSON_ID>
   <EMPLOYEE_ID>032656300</EMPLOYEE_ID>
   <EFFECTIVE_START_DATE>2020-08-19 00:00:00.000</EFFECTIVE_START_DATE>
   <EFFECTIVE_END_DATE>4712-12-31 00:00:00.000</EFFECTIVE_END_DATE>
   <BUSINESS_PROCESS>Absence Return for XXXXXXXXX last day of absence on 08/18/2020, first day back at work on 08/19/2020</BUSINESS_PROCESS>
   <ACT_ASSIGNMENT_STATUS_TYPE_ID>1</ACT_ASSIGNMENT_STATUS_TYPE_ID>
   <ACT_ORGANIZATION_ID>601</ACT_ORGANIZATION_ID>
   <ACT_JOB_QUINTIQ_POSITION>Trainee</ACT_JOB_QUINTIQ_POSITION>
   <ACT_HOURS_PER_WEEK>37.5</ACT_HOURS_PER_WEEK>
   <ACT_HOURS_FREQUENCY>W</ACT_HOURS_FREQUENCY>
   <ACT_BARGAINING_UNIT_CODE>C</ACT_BARGAINING_UNIT_CODE>
   <ACT_PRIMARY_PROVINCE>BC</ACT_PRIMARY_PROVINCE>
</EMPLOYEE_ASSIGNMENT>
<EMPLOYEE_ASSIGNMENT>
   <REFRESH_DATE>2022-03-10 10:55:35.000</REFRESH_DATE>
   <PERSON_ID>11189</PERSON_ID>
   <EMPLOYEE_ID>032656300</EMPLOYEE_ID>
   <EFFECTIVE_START_DATE>2020-08-19 00:00:00.000</EFFECTIVE_START_DATE>
   <EFFECTIVE_END_DATE>4712-12-31 00:00:00.000</EFFECTIVE_END_DATE>
   <BUSINESS_PROCESS>Data Change: XXXXXXXXXXXX</BUSINESS_PROCESS>
   <ACT_ASSIGNMENT_STATUS_TYPE_ID>1</ACT_ASSIGNMENT_STATUS_TYPE_ID>
   <ACT_ORGANIZATION_ID>856</ACT_ORGANIZATION_ID>
   <ACT_JOB_QUINTIQ_POSITION>Employee</ACT_JOB_QUINTIQ_POSITION>
   <ACT_HOURS_PER_WEEK>37.5</ACT_HOURS_PER_WEEK>
   <ACT_HOURS_FREQUENCY>W</ACT_HOURS_FREQUENCY>
   <ACT_BARGAINING_UNIT_CODE>C</ACT_BARGAINING_UNIT_CODE>
   <ACT_PRIMARY_PROVINCE>MB</ACT_PRIMARY_PROVINCE>
</EMPLOYEE_ASSIGNMENT>

</root>

The two nodes have the same EFFECTIVE_START_DATE but different BUSINESS_PROCESS for a single EMPLOYEE_ID. I need to transform that XML in a way that: when Two (or more) BUSINESS_PROCESS are present for and EMPLOYEE_ID on the same EFFECTIVE_START_DATE it shows only the one that is of value Data Change: XXXXXXXXX. I need to transform it to:

<?xml version="1.0" encoding="UTF-8"?>
<root>
   <EMPLOYEE_ASSIGNMENT>
   <REFRESH_DATE>2022-03-10 10:55:35.000</REFRESH_DATE>
   <PERSON_ID>11189</PERSON_ID>
   <EMPLOYEE_ID>032656300</EMPLOYEE_ID>
   <EFFECTIVE_START_DATE>2020-08-19 00:00:00.000</EFFECTIVE_START_DATE>
   <EFFECTIVE_END_DATE>4712-12-31 00:00:00.000</EFFECTIVE_END_DATE>
   <BUSINESS_PROCESS>Data Change: XXXXXXXXXXXX</BUSINESS_PROCESS>
   <ACT_ASSIGNMENT_STATUS_TYPE_ID>1</ACT_ASSIGNMENT_STATUS_TYPE_ID>
   <ACT_ORGANIZATION_ID>856</ACT_ORGANIZATION_ID>
   <ACT_JOB_QUINTIQ_POSITION>Employee</ACT_JOB_QUINTIQ_POSITION>
   <ACT_HOURS_PER_WEEK>37.5</ACT_HOURS_PER_WEEK>
   <ACT_HOURS_FREQUENCY>W</ACT_HOURS_FREQUENCY>
   <ACT_BARGAINING_UNIT_CODE>C</ACT_BARGAINING_UNIT_CODE>
   <ACT_PRIMARY_PROVINCE>MB</ACT_PRIMARY_PROVINCE>
</EMPLOYEE_ASSIGNMENT>

</root>

Thanks a lot

xsltgroup-bytransform


Solution 1:[1]

This is difficult to follow. If (!) I understand your description correctly, you want to do:

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:template match="/root">
    <xsl:copy>
        <xsl:for-each-group select="EMPLOYEE_ASSIGNMENT" group-by="concat(EMPLOYEE_ID,'|', EFFECTIVE_START_DATE)">
            <xsl:copy-of select="current-group()[starts-with(BUSINESS_PROCESS, 'Data Change:')]"/>
        </xsl:for-each-group>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 michael.hor257k

Related Questions