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