Remove Duplicate records from XML in SAP CPI using XSLT

[abdulbasithshaik]

Hi Everyone,

We are trying to code in one requirement in SAP CPI using XSLT.

Requirement : Remove duplicates nodes from XML which has same FirstName,LastName and DateofBirth and Status not A.

Problem Statement : When 2 or more nodes have same firstname,lastname and dateof birth but for one of the nodes Status is not A its coming in output.

Xslt used:

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

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<!-- Match the root element and create a new root element for the result. -->

<xsl:template match="/">

<response>

<!-- Apply the custom template to process the record nodes. -->

<xsl:apply-templates select="//record"/>

</response>

</xsl:template>

<!-- Template to process record nodes -->

<xsl:template match="record">

<!-- Check if the current record node meets the conditions. -->

<xsl:if test="(count(/response/record/FirstName = current()/response/record/FirstName and /response/record/LastName = current()/response/record/LastName and /response/record/DateofBirth = current()/response/record/DateofBirth) > 1 and /response/record/CustomCategoryValue9 = 'A') or (count(/response/record/FirstName = current()/response/record/FirstName and /response/record/LastName = current()/response/record/LastName and /response/record/DateofBirth = current()/response/record/DateofBirth) = 1)">

<!-- Copy the current record node to the result. -->

<xsl:copy-of select="."/>

</xsl:if>

</xsl:template>

</xsl:stylesheet>

Scenario :1

Xml input (Expected output should contain only first record if the selection criteria is matching):

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

<response>

<record>

<LastName>"LastName"</LastName>

<FirstName>"FirstName"</FirstName>

<DateofBirth>"01-01-2020"</DateofBirth>

<Status>"A"</Status>

</record>

<record>

<LastName>"LastName"</LastName>

<FirstName>"FirstName"</FirstName>

<DateofBirth>"01-01-2020"</DateofBirth>

<Status>"B"</Status>

</record>

</response>

Scenario 2:

Xml input (Expected output should contain both first and second record as Status for both records is 'A' though the field values are same):

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

<response>

<record>

<LastName>"LastName"</LastName>

<FirstName>"FirstName"</FirstName>

<DateofBirth>"01-01-2020"</DateofBirth>

<Status>"A"</Status>

</record>

<record>

<LastName>"LastName"</LastName>

<FirstName>"FirstName"</FirstName>

<DateofBirth>"01-01-2020"</DateofBirth>

<Status>"A"</Status>

</record>

</response>

NOTE : Along with above 4 record node wil have n number of attributes in it which are not mentioned here.

Kindly help us out by identifying where we are going wrong

[asutoshmaharana2326]

Dear Abdul,

Please find below code using XSLT 2.0. But i have a doubt what to do in case you have only records with non "A" status? Here i assumed you need only first record in such cases.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="response">
    <xsl:copy>
      <!-- Loop through each group with same LastName FirstName and DateofBirth -->
      <xsl:for-each-group select="record" group-by="concat(LastName, '|', FirstName, '|', DateofBirth)">
        <!-- Define the double quoted A value -->
        <xsl:variable name="quotedA">"A"</xsl:variable>

        <!-- Separate records into groups based on Status -->
        <xsl:variable name="groupA" select="current-group()[Status = $quotedA]"/>
        <xsl:variable name="groupNotA" select="current-group()[Status != $quotedA]"/>

        <!-- Apply logic -->
        <xsl:choose>
          <!-- Case 1: Only 'A' status group exists -->
          <xsl:when test="count($groupA) > 0 and not($groupNotA)">
            <xsl:copy-of select="$groupA"/>
          </xsl:when>
          <!-- Case 2: Both 'A' and non-'A' status groups exist -->
          <xsl:when test="count($groupA) > 0 and count($groupNotA) > 0">
            <xsl:copy-of select="$groupA"/>
          </xsl:when>
          <!-- Case 3: Only non-'A' status group exists -->
          <xsl:otherwise>
            <xsl:copy-of select="$groupNotA[1]"/>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:for-each-group>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

Thanks,Asutosh

[abdulbasithshaik]

Thanks for prompt response.

Let me clarify more on the requirement.

The status can be anything (A, B, C ..). We would need to identify the duplicate records who have same FirstName, LastName, and DateofBirth combination and pick only one record which has status as 'A' and exclude other records. However the other records which were not duplicate should still be part of the output irrespective of status value.

Other condition is that if there are two records with same FirstName, LastName and DateofBirth but with same status then both should be returned.

Hope this helps.

[asutoshmaharana2326]

I am still not getting it.

However the other records which were not duplicate should still be part of the output irrespective of status value. ----> This is working with the above code.

Now let's come to status condition.

1. status can have any value.

2. if same firstname, lastname and dob found then check status field.

2.1. either all of those reocrds have same status or different.

if all have different status then return all the records or only records having status as "A" ??

what to do if all are having same status??

[abdulbasithshaik]

1. status can have any value.-Yes status can have any value. 2. if same firstname, lastname and dob found then check status field.-correct: check status condition only if duplicates are present. 2.1. either all of those reocrds have same status or different. if all have different status then return all the records or only records having status as "A" ?? what to do if all are having same status??-- if there are duplicates, all those records with status A should be included in final output and other records should be removed.For example if all are A then all should come, if 2 are A and one is T then 2 records with status A should come