Help on mapping

[Former Member]

Hello,

I need to develop a message mapping in order to achieve the following scenario:

Input Message Type

MT_DATA1

-row (0..unbounded)

-TABLE1_ID

-TABLE1_FIELD1

-TABLE1_FIELD2

-TABLE2_ID

-TABLE2_FIELD1

-TABLE2_FIELD2

-TABLE2_FIELD3

Output Message Type

MT_DATA2

-TABLE1 (1..unbounded)

-ID

-FIELD1

-FIELD2

-TABLE2 (1..unbounded)

-ID

-FIELD1

-FIELD2

The input message type is a result of an sql left outer join between TABLE1 and TABLE2. The message type MT_DATA1 is already sorted by TABLE1_ID.

Can you help me?

Thanks,

Pedro Leal

[Former Member · Accepted solution]

Hi,

use the below mapping program

1. TableID -> exists(check whether the field exists) -> Create Node -> table.

2. Create UDF to map the ID value and Field.

Yuga

[Former Member · Accepted solution]

Probably its easier to understand the desired scenario with the following xml structures:

MT_DATA1 is the source message type and MT_DATA2 is the target message type.

MT_DATA1

<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_DATA1 xmlns:ns0="http://test.pt/xi">
   <row>
      <TABLE1_PK>pk1</TABLE1_PK>
      <TABLE1_FIELD1>aa</TABLE1_FIELD1>
      <TABLE1_FIELD2>aa</TABLE1_FIELD2>
      <TABLE2_PK>pk1</TABLE2_PK>
      <TABLE2_FIELD1>bb</TABLE2_FIELD1>
      <TABLE2_FIELD2>bb</TABLE2_FIELD2>
      <TABLE2_FIELD3>bb</TABLE2_FIELD3>
   </row>
   <row>
      <TABLE1_PK>pk1</TABLE1_PK>
      <TABLE1_FIELD1>aa</TABLE1_FIELD1>
      <TABLE1_FIELD2>aa</TABLE1_FIELD2>
      <TABLE2_PK>pk1</TABLE2_PK>
      <TABLE2_FIELD1>cc</TABLE2_FIELD1>
      <TABLE2_FIELD2>cc</TABLE2_FIELD2>
      <TABLE2_FIELD3>cc</TABLE2_FIELD3>
   </row>
   <row>
      <TABLE1_PK>pk2</TABLE1_PK>
      <TABLE1_FIELD1>xx</TABLE1_FIELD1>
      <TABLE1_FIELD2>xx</TABLE1_FIELD2>
      <TABLE2_PK>pk2</TABLE2_PK>
      <TABLE2_FIELD1>zz</TABLE2_FIELD1>
      <TABLE2_FIELD2>zz</TABLE2_FIELD2>
      <TABLE2_FIELD3>zz</TABLE2_FIELD3>
   </row>
</ns0:MT_DATA1>

MT_DATA2

<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_DATA2 xmlns:ns0="http://test.pt/xi">

	<TABLE1>
		<PK>pk1</PK>
		<FIELD1>aa</FIELD1>
		<FIELD2>aa</FIELD2>
		<TABLE2>
			<PK>pk1</PK>
			<FIELD1>bb</FIELD1>
			<FIELD2>bb</FIELD2>
			<FIELD3>bb</FIELD3>
		</TABLE2>
		<TABLE2>
			<PK>pk1</PK>
			<FIELD1>cc</FIELD1>
			<FIELD2>cc</FIELD2>
			<FIELD3>cc</FIELD3>
		</TABLE2>
	</TABLE1>
	<TABLE1>
		<PK>pk2</PK>
		<FIELD1>xx</FIELD1>
		<FIELD2>xx</FIELD2>
		<TABLE2>
			<PK>pk2</PK>
			<FIELD1>zz</FIELD1>
			<FIELD2>zz</FIELD2>
			<FIELD3>zz</FIELD3>
		</TABLE2>
	</TABLE1>
</ns0:MT_DATA2>

Can you help me?

Thanks,

Pedro Leal

[Former Member · Accepted solution]

Hi,

you need to map the fields as below

<row> -----> <Table1>

 <TABLE1_PK> --------><PK>
 <TABLE1_FIELD1>--------><FIELD1>
 <TABLE1_FIELD2>--------><FIELD2>


<TABLE1_PK> as a --> testUDF --> <TABLE2>
<TABLE2_PK> as b---> 
<TABLE2_PK> as c---> 

USE Below CODE for testUDF

Cache Parameter = queue

// write code here

String temp = new String(a[0]);

for(int i =0; a.length>i; i++)
{
 for(int j=0; b.length>j;j++)
  {
     if(temp.equals(b[j]))
	result.addValue(c[j]);

  }		
}

<TABLE1_PK> as a --> testUDF -->SplitByValue---> <TABLE2>--><pk>
<TABLE2_PK> as b---> 
<TABLE2_PK> as c--->  


<TABLE1_PK> as a --> testUDF -->SplitByValue---> <TABLE2>--><field1>
<TABLE2_PK> as b---> 
<TABLE2_field1> as c--->  

same way map other fields of <TABLE2>

Thanks

Swarup

Edited by: Swarup Sawant on Aug 22, 2008 4:37 PM

[Former Member · Accepted solution]

It still doesn't work.

Any suggestions?