Merge two xml and get output xml

Papillon28 86 Reputation points
2020-09-29T13:37:59.277+00:00

Hi All,
I need help on merge xml files using SSIS. I have files as below.

part1.xml
<?xml version="1.0" encoding="utf-8"?>
<RequestRoot>
<header>
<totalRecordCount>2</totalRecordCount>
<fileCreationDate>2014-12-06</fileCreationDate>
<correlationId>1</correlationId>
</header>
</RequestRoot>

Part2.xml
<?xml version="1.0" encoding="utf8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
<RequestRoot>
<IBRequest>
<List>
<recordCount>1</recordCount>
<postRecord>
<recordId>1</recordId>
<postAdjustment>
<targetId>
<MemId>100000S01</MemId>
</targetId>
<Value>
<money>2.35</money>
</Value>
<Plan>Change</Plan>
</postAdjustment>
</postRecord>
</List>
</IBRequest>
</RequestRoot>
</soapenv:Body>
</soapenv:Envelope>

Output file as
main part.xml
<?xml version="1.0" encoding="utf-8" ?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
<RequestRoot>
<header>
<totalRecordCount>2</totalRecordCount>
<fileCreationDate>2014-12-06</fileCreationDate>
<correlationId>1</correlationId>
</header>
<IBRequest>
<List>
<recordCount>1</recordCount>
<postRecord>
<recordId>1</recordId>
<postAdjustment>
<targetId>
<MemId>100000S01</MemId>
</targetId>
<Value>
<money>2.35</money>
</Value>
<Plan>Change</Plan>
</postAdjustment>
</postRecord>
</List>
</IBRequest>
</RequestRoot>
</soapenv:Body>
</soapenv:Envelope>

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,504 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 25,726 Reputation points
    2020-09-29T18:18:55.057+00:00

    Hi @Papillon28 ,

    You need to run SSIS XML Task, operation XSLT transformation.

    I had to fix the XML prolog in the Part2.xml file. It didn't have dash in the utf-8 value. 

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

    XSLT
    SSIS XML Task, operation Validation requires a fully qualified path for the Part1.xml file in the XSLT: document('e:\temp\part1.xml')

    XSLT should process the Part2.xml file 

    <?xml version="1.0"?>  
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">  
 <xsl:output method="xml" indent="yes" encoding="utf-8" omit-xml-declaration="no"/>  
  
 <xsl:strip-space elements="*"/>  
  
 <xsl:template match="node()|@*">  
 <xsl:copy>  
 <xsl:apply-templates select="node()|@*"/>  
 </xsl:copy>  
 </xsl:template>  
  
 <xsl:template match="RequestRoot">  
 <RequestRoot>  
 <xsl:copy-of select="document('e:\temp\part1.xml')/RequestRoot/header"/>  
 <xsl:apply-templates/>  
 </RequestRoot>  
 </xsl:template>  
</xsl:stylesheet>

    29406-ssis-xslt.jpg

    29441-xml-xslt-result.png

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-30T02:21:48.253+00:00

    Hi @Papillon28 ,

    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

    Please refer to How to join two XML files based on an element in SSIS.

    Best Regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments