Skip to Content
0
Jun 19, 2020 at 07:55 AM

How to use Message Mapping with relational data data through jdbc connection in Process Integration

37 Views

I have a simple problem that is causing issues to implement in PI.

I have a database with a header and a detail table with a foreign key of headerid connecting them. Each header has multiple details.I want this to go into a similar data type in the receiver side with multiple details for each header . So I thought this should be easy as the 2 data types were identical.

Unfortunately I connect to a DB via a jdbc but can only send back a denormalized set of data. The jdbc doesnt allow you to send back anything hierarchical or in xml. My understanding is that the only result is in the format

MT_Source
Rows.... 0..n
Field1
Field2
Fieldn

Basically I have select h.col1,h.col2,d.col1,d.col2.. from header h join detail d on h.headerid=d.headerid and this produces results like

h1col1,h1col2,d1col1,d1.col2..

h1col1,h1col2,d2col1,d2.col2..

h2col2,h2col2,d3col1,d3.col2..

h2col1,h2col2,d4col1,d4.col2..

....

Now my problem is i I now want to map it back to something of the format

<header>

<col1>

<col2>

<detail>
<col1>
<col2>

</detail>

</header>

How do I do this is message mapping. How can i take a denormalised set of data and put it back into a xml hierarchy? Im not an expert in Message Mapping but am pretty competent

Is there a better approach? It seems that I am stuck with a non hierarchical xml from the jdbc. So if i have 3 headers each with 4 details I have 12 rows, and I don't know how to create 3 headers each with 4 rows.

I know this is basically a message mapping question about denormalising so if ayone can point to a blog that does this that would be appreciated