Skip to Content

Mapping - Master/detail, match rows by key

Hi there,

Got new user ID, no history, and starting with a question.. oh well

Kinda new to PI/PO and having problem with mapping.

Rows of two different tables (MASTER_IN, DETAIL_IN) are on the same level and need

to be mapped in such a way that detail section will be nested inside master section.

Assumptions:

- rows can be joined by a key field MASTER_IN-KEY = DETAIL_IN-KEY_MASTER,

- for each MASTER_IN row there can be zero or more DETAIL_IN rows [ 0 .. n ],

- for each MASTER_IN row there will one corresponding MASTER row [ 1 : 1 ]

- XML input and output structures attached.

--

Also what would be the most efficient way to develop such a mapping bearing in mind it would have to cover 1 master and around 10 detail tables ?

Thanks,

Bart

--

MT_IN - input structure

MT_OUT - output structure

<ns0:MT_IN xmlns:ns0="urn:2b.pl:EXT:Finances:MappingTest">
   <MASTER_IN>
      <KEY>1</KEY>
      <DATA_1>DATA_1_1</DATA_1>
      <DATA_2>DATA_1_2</DATA_2>
   </MASTER_IN>
   <MASTER_IN>
      <KEY>2</KEY>
      <DATA_1>DATA_2_1</DATA_1>
      <DATA_2>DATA_2_2</DATA_2>
   </MASTER_IN>
   <DETAIL_IN>
      <KEY_MASTER>1</KEY_MASTER>
      <KEY>A</KEY>
      <DATA_1>DATA_1_A_1</DATA_1>
      <DATA_2>DATA_1_A_2</DATA_2>
   </DETAIL_IN>
   <DETAIL_IN>
      <KEY_MASTER>1</KEY_MASTER>
      <KEY>B</KEY>
      <DATA_1>DATA_1_B_1</DATA_1>
      <DATA_2>DATA_1_B_2</DATA_2>
   </DETAIL_IN>
</ns0:MT_IN>
<ns0:MT_OUT xmlns:ns0="urn:2b.pl:EXT:Finances:MappingTest">
   <MASTER>
      <MASTER_KEY>1</MASTER_KEY>
      <MASTER_DATA_1>DATA_1_1</MASTER_DATA_1>
      <MASTER_DATA_2>DATA_1_2</MASTER_DATA_2>
      <DETAIL>
	<DETAIL_KEY>A</DETAIL_KEY>
	<DETAIL_DATA_1>DATA_1_A_1</DETAIL_DATA_1>
	<DETAIL_DATA_2>DATA_1_A_2</DETAIL_DATA_2>
      </DETAIL>
      <DETAIL>
	<DETAIL_KEY>B</DETAIL_KEY>
	<DETAIL_DATA_1>DATA_1_B_1</DETAIL_DATA_1>
	<DETAIL_DATA_2>DATA_1_B_2</DETAIL_DATA_2>
      </DETAIL>	  
   </MASTER>
   <MASTER>
      <MASTER_KEY>2</MASTER_KEY>
      <MASTER_DATA_1>DATA_2_1</MASTER_DATA_1>
      <MASTER_DATA_2>DATA_2_2</MASTER_DATA_2>
      <DETAIL />
   </MASTER>
</ns0:MT_OUT>
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    May 04, 2017 at 03:04 PM

    Hi Bartozs!

    You could try with XSL transformation. Your mapping doesn't look much complicated at the first sight.

    When applying transformation like this:

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
        <xsl:template match="/">
            <ns0:MT_OUT xmlns:ns0="urn:2b.pl:EXT:Finances:MappingTest">
                <xsl:apply-templates select="//MASTER_IN"/>
            </ns0:MT_OUT>
        </xsl:template>
        <xsl:template match="MASTER_IN">
            <xsl:variable name="masterkey" select="KEY"/>
            <MASTER>
                <MASTERKEY><xsl:value-of select="KEY"/></MASTERKEY>
                <MASTER_DATA_1><xsl:value-of select="DATA_1"/></MASTER_DATA_1>
                <MASTER_DATA_2><xsl:value-of select="DATA_2"/></MASTER_DATA_2>
                <xsl:for-each select="//DETAIL_IN[KEY_MASTER = $masterkey]">
                    <DETAILS>
                        <DETAIL_KEY><xsl:value-of select="KEY"/></DETAIL_KEY>
                        <DETAIL_DATA_1><xsl:value-of select="DATA_1"/></DETAIL_DATA_1>
                        <DETAIL_DATA_2><xsl:value-of select="DATA_2"/></DETAIL_DATA_2>
                    </DETAILS>
                </xsl:for-each>
            </MASTER>
        </xsl:template>
    </xsl:stylesheet>

    result is:

    <?xml version="1.0" encoding="utf-8"?>
    <ns0:MT_OUT xmlns:ns0="urn:2b.pl:EXT:Finances:MappingTest">
        <MASTER>
            <MASTERKEY>1</MASTERKEY>
            <MASTER_DATA_1>DATA_1_1</MASTER_DATA_1>
            <MASTER_DATA_2>DATA_1_2</MASTER_DATA_2>
            <DETAILS>
                <DETAIL_KEY>A</DETAIL_KEY>
                <DETAIL_DATA_1>DATA_1_A_1</DETAIL_DATA_1>
                <DETAIL_DATA_2>DATA_1_A_2</DETAIL_DATA_2>
            </DETAILS>
            <DETAILS>
                <DETAIL_KEY>B</DETAIL_KEY>
                <DETAIL_DATA_1>DATA_1_B_1</DETAIL_DATA_1>
                <DETAIL_DATA_2>DATA_1_B_2</DETAIL_DATA_2>
            </DETAILS>
        </MASTER>
        <MASTER>
            <MASTERKEY>2</MASTERKEY>
            <MASTER_DATA_1>DATA_2_1</MASTER_DATA_1>
            <MASTER_DATA_2>DATA_2_2</MASTER_DATA_2>
        </MASTER>
    </ns0:MT_OUT>

    Regards, Evgeniy.

    Add comment
    10|10000 characters needed characters exceeded

  • May 23, 2017 at 08:15 AM

    Sorry for the delay, development priorities changed a bit.

    That should do for now, thanks.

    Add comment
    10|10000 characters needed characters exceeded