Skip to Content
0

Mapping - Master/detail, match rows by key

May 04, 2017 at 01:51 PM

59

avatar image

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>
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Evgeniy Kolmakov May 04, 2017 at 03:04 PM
1

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.

Share
10 |10000 characters needed characters left characters exceeded
Bartosz Bijak May 23, 2017 at 08:15 AM
0

Sorry for the delay, development priorities changed a bit.

That should do for now, thanks.

Share
10 |10000 characters needed characters left characters exceeded