How to compare 2 stage files and find out unique values from stage file 1 in OIC XSLT Mapper?

Question

I have two stage files where I have to compare Job_Title from file 1 with JOB_NAME of file 2, I have to display the unique Job_Title values from file 1. My code is working as expected

File 1:

'

<ReadResponse xmlns="http://xmlns.oracle.com/cloud/adapter/stagefile/ReadFilteredInputFile_REQUEST/types">
  <Records xmlns="http://TargetNamespace.com/fileReference/ReadFilteredInputFile">
    <Record>
      <Last_Name>Abbassi</Last_Name>
      <First_Name>Bob</First_Name>
      <Work_Contact_Work_Email>[email protected]</Work_Contact_Work_Email>
      <Hire_Date>6/25/2012</Hire_Date>
      <Job_Title_Description>ASSOCIATE DIRECTOR</Job_Title_Description>
      <Job_Title_Code>ADIR</Job_Title_Code>
      <Home_Department_Description>Finance</Home_Department_Description>
      <Home_Department>121</Home_Department>
      <Rehire_Date></Rehire_Date>
      <Termination_Date></Termination_Date>
      <Position_ID>DXM001019</Position_ID>
      <Position_Status>Active</Position_Status>
      <Reports_To_Position_ID>DXM001077</Reports_To_Position_ID>
      <Reports_To_Name>Lee, Todd</Reports_To_Name>
      <This_is_a_Management_position>No</This_is_a_Management_position>
      <Job_Title>Associate Director, FP&amp;A</Job_Title>
    </Record>
    <Record>
      <Last_Name>Abejuela</Last_Name>
      <First_Name>Jowdy</First_Name>
      <Work_Contact_Work_Email>[email protected]</Work_Contact_Work_Email>
      <Hire_Date>12/9/2019</Hire_Date>
      <Job_Title_Description>SENIOR MANAGER</Job_Title_Description>
      <Job_Title_Code>SRMG</Job_Title_Code>
      <Home_Department_Description>Quality General</Home_Department_Description>
      <Home_Department>461</Home_Department>
      <Rehire_Date></Rehire_Date>
      <Termination_Date></Termination_Date>
      <Position_ID>DXM001514</Position_ID>
      <Position_Status>Active</Position_Status>
      <Reports_To_Position_ID>DXM001629</Reports_To_Position_ID>
      <Reports_To_Name>Golden, Kihlon</Reports_To_Name>
      <This_is_a_Management_position>No</This_is_a_Management_position>
      <Job_Title>Senior Manager, QA</Job_Title>
    </Record>
    <Record>
      <Last_Name>Acosta</Last_Name>
      <First_Name>Andrea</First_Name>
      <Work_Contact_Work_Email>[email protected]</Work_Contact_Work_Email>
      <Hire_Date>5/18/2020</Hire_Date>
      <Job_Title_Description>RESEARCH ASSOCIATE</Job_Title_Description>
      <Job_Title_Code>RA</Job_Title_Code>
      <Home_Department_Description>Analytical</Home_Department_Description>
      <Home_Department>442</Home_Department>
      <Rehire_Date></Rehire_Date>
      <Termination_Date></Termination_Date>
      <Position_ID>DXM001536</Position_ID>
      <Position_Status>Active</Position_Status>
      <Reports_To_Position_ID>DXM001026</Reports_To_Position_ID>
      <Reports_To_Name>Kuehne, Henriette</Reports_To_Name>
      <This_is_a_Management_position>No</This_is_a_Management_position>
      <Job_Title>Research Associate</Job_Title>
    </Record>
  </Records>
</ReadResponse>

'

File 2: '

<ReadResponse xmlns="http://xmlns.oracle.com/cloud/adapter/stagefile/ReadDecodedOTBIJobsResponse_REQUEST/types">
  <JobSet xmlns="http://TargetNamespace.com/fileReference/ReadDecodedOTBIJobsResponse">
    <Job>
      <JOB_ID>300000004123258</JOB_ID>
      <JOB_CODE>0002</JOB_CODE>
      <JOB_NAME>Senior Manager, QA</JOB_NAME>
    </Job>
    <Job>
      <JOB_ID>300000004123195</JOB_ID>
      <JOB_CODE>0001</JOB_CODE>
      <JOB_NAME>Associate Director, FP&amp;A</JOB_NAME>
    </Job>
  </JobSet>
</ReadResponse>

' Expected output: '

<JobSet>
    <Job>
      <Job_Title>Research Associate</JobTitle>
    </Job>
  </JobSet>

'

My code:

<ns50:NewJobSet>
<xsl:for-each select="$ReadFilteredInputFile/nsmpr8:ReadResponse/nsmpr3:Records/nsmpr3:Record">
<xsl:variable name="jobTitle" select="nsmpr3:Job_Title"/>
<xsl:for-each select="$ReadDecodedOTBIJobsResponse/nsmpr7:ReadResponse/ns37:JobSet/ns37:Job">
<xsl:variable name="jobName" select="ns37:JOB_NAME"/>
<xsl:choose>
<xsl:when test="ns37:JOB_NAME = $jobTitle"/>
<xsl:otherwise>
<ns50:NewJob>
<ns50:Job_Title>
<xsl:value-of select="$jobName"/>
</ns50:Job_Title>
</ns50:NewJob>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</xsl:for-each>
</ns50:NewJobSet>

My code is returning duplicates incase if there any matching records between two file, Could anyone please help to fix this ?

Answer 1

I would use a key

<xsl:key name="jobs" match="Job" use="JOB_NAME" xpath-default-namespace="http://TargetNamespace.com/fileReference/ReadDecodedOTBIJobsResponse"/>

and then check e.g.

  <xsl:template match="Record/Job_Title[not(key('jobs', ., $jobs))]" xpath-default-namespace="http://TargetNamespace.com/fileReference/ReadFilteredInputFile">
    <Job_Title>
      <xsl:value-of select="."/>
    </Job_Title>
  </xsl:template>

where $jobs is the variable holding your second document.

And an empty template for those elements that have references in the second file e.g. <xsl:template match="Record/Job_Title[key('jobs', ., $jobs)]" xpath-default-namespace="http://TargetNamespace.com/fileReference/ReadFilteredInputFile"/>