2

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?

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Balaji Pooruli
  • 240
  • 1
  • 3
  • 16

1 Answers1

1

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:

    Or you can use a third party component:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thanks for your response. However, I am not looking for a XML destination component. I am looking for a component that I can use to join two XML files, just like joining two SQL server tables. It appears that there isn't one. – Balaji Pooruli May 30 '19 at 18:02
  • @BalajiPooruli follow the first 3 steps only – Hadi May 30 '19 at 22:37
  • Thanks for your help. I tried the first 3 steps and it worked perfectly. – Balaji Pooruli May 31 '19 at 20:51