How to join two XML files based on an element in SSIS?

I have two XML files, one with 2 elements and the other file with 4 elements. One of them is common between the two files. I would like to pull the contents of both XML files, do a join with the common element (like an inner join between two SQL server tables) and then dump the output of the columns in both files into one output file. Below is the content of file1.xml.

<?xml version="1.0" standalone="yes"?>
<DataPairs>
<DataPair>
<LicenseNumber>12345</LicenseNumber>
<DataValue>12331234</DataValue>
</DataPair>
<DataPair>
<LicenseNumber>43432</LicenseNumber>
<DataValue>435432134</DataValue>
</DataPair>
</DataPairs>

Below is the content of file2.xml

<LastRecords>
<DataRecord><AgentNum>013512</AgentNum><EmpId>124532523</EmpId><LicenseKey>12345</LicenseKey></DataRecord>
<DataRecord><AgentNum>013512</AgentNum><EmpId>124532523</EmpId><LicenseKey>12345</LicenseKey></DataRecord>
</LastRecords>

I would like to do a join between the above files (like INNER JOIN). Whereever the LicenseNumber from file1 is matching with LicenseKey in file2.xml, I would like to output the following values into a SQL server table.

LicenseKey
AgentId
EmplId
DataValue

I have tried using join, join merge, lookup, etc in Visual Studio 2017 by creating an SSIS package, but I don't see any component that can help me merge two xml files to get the results I want.

I am looking for a SSIS component to merge two XML files based on a common element, but I can't seem to find one. I did google search and also looked at couple of youtube videos, but to no avail.

Can someone help?

Unfortunately, there is no official XML destination component found in Integration Services. There are some third party component or you can use a workaround to write to a XML file.

In order to merge two Xml files you have to follow these steps:

1. Add 2 XML Source component (one for each file)
2. Add a sort component after each XML source and select the LicenseNumber and LicenseKey as Sorting columns
3. Add a Merge Join component to merge both flows

4. To store data in XML file you can follow one of the following links:

   • Yet another SSIS XML Destination
   • An “secret” SSIS XML Destination Provider you might not found yet (You need to import data to a staging table first)
   • How to export data from database tables to an XML file using SSIS (staging table needed)

   Or you can use a third party component:

   • Task Factory Feature: SSIS XML Components
   • XML SSIS Destination