Merge two xml files based on key field in CPI

[former_member217440]

Hello Experts,

I have requirement two merge two xml files. Based on the key field in the files I want to merge entire content of the xml 1 to xml 2. Could you please help me to achieve this scenario.

XML1.

<Record>
    <Detail>
        <Key>1</Key>
        <Place>Ocean</Place>
        <City>Urban</City>
    </Detail>
    <Detail>
        <Key>2</Key>
        <Place>Road</Place>
        <City>Rural</City>
    </Detail>
    <Detail>
        <Key>3</Key>
        <Place>Plane</Place>
        <City>Semiurban</City>
    </Detail>
</Record>


XML2

<Record>
    <Contact>
        <Key>1</Key>
        <Name>Jack</Name>
    </Contact>
    <Contact>
        <Key>2</Key>
        <Name>Ethan</Name>
    </Contact>
    <Contact>
        <Key>3</Key>
        <Name>Ron</Name>
    </Contact>
</Record>

And the expected output.

    <Record>
        <Contact>
            <Key>1</Key>
            <Name>Jack</Name>
            <Place>Ocean</Place>
            <City>Urban</City>
        </Contact>
        <Contact>
            <Key>2</Key>
            <Name>Ethan</Name>
            <Place>Road</Place>
            <City>Rural</City>
        </Contact>
        <Contact>
            <Key>3</Key>
            <Name>Ron</Name>
            <Place>Plane</Place>
            <City>Semiurban</City>
        </Contact>
    </Record>

[MortenWittrock]

Hi Roshan

What have you tried so far and how did it not work?

Regards,

Morten

[former_member217440]

Hi Morten,

Currently I am fetching the two xmls from two different folders.

1. For XML1, I am storing the payload to exchange property eg. P_XML1.
2. After that reading XML2.
3. Using XSLT mapping to pass P_XML1 as parameter and parsing it into XML.

Using this XSLT mapping.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:output method="xml" indent="yes" />
    <xsl:mode on-no-match="shallow-copy" />
    <xsl:param name="P_XML1" />
    <xsl:variable name="details-doc" select="parse-xml($P_XML1)" />

    <xsl:template match="Key">
        <xsl:copy-of select="." />
        <xsl:variable name="key" select="string(.)" />
        <xsl:copy-of select="$details-doc/Record/Detail[Key = $key]/Name" />

    </xsl:template></xsl:stylesheet>

[former_member217440 · Accepted solution]

Hi,

I was able to solve the issue. Below is the code for the same.

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

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

<xsl:output method="xml" indent="yes" />

<xsl:mode on-no-match="shallow-copy" />

<xsl:param name="P_XML1" />

<xsl:variable name="details-doc" select="parse-xml($P_XML1)" />

<xsl:template match="Key">

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

<xsl:variable name="key" select="string(.)" />

<xsl:copy-of select="$details-doc/Record/Detail[Key = $key]/Place" />

<xsl:copy-of select="$details-doc/Record/Detail[Key = $key]/City" />

</xsl:template>

</xsl:stylesheet>

[Sreenith · Accepted solution]

nice one