cancel
Showing results for 
Search instead for 
Did you mean: 

Message Mapping Nested Structure to key node in JDBC XML SQL Structure

Former Member
0 Kudos

Hello everybody,

I'm facing the next problem, I have the following nested structure

<ns0:MT_Irdoc_ArchivoNested xmlns:ns0="http://ref.pemex.com/PI/FI/ArchivoIrdoc">

<Header id_consecutivo="">

<Transaction/>

<Header2/>

<Header3/>

<Header4/>

<Header5/>

<Detail>

<Transaction/>

<Detail2/>

<Detail3/>

<Detail4/>

<Detail5/>

</Detail>

</Header>

<NombreArchivo/>

</ns0:MT_Irdoc_ArchivoNested>

as you can see, Detail is a nested Node from Header Node, and Detail is with occurrence 0...unbounded, I need to generate a SELECT XML SQL Structure as follows

<StatementName>

<dbTableName action=u201DSELECTu201D>

<table>realDbTableName</table>

<access>

<col1/>

<col2/>

<col3/>

</access>

<key1>

<col4>val4</col4>

<col5>val5</col5>

<col6>val6</col6>

</key1>

</dbTableName>

</StatementName>

now my problem is that col4 and col6 are values mapped from Header fields values, and col5 mus be mapped from Detail2 field of Detail Nested Structure, and Detail is 0...unbounded so it can contain any number of values and must included in the key structure, I tried by mapping the Detail Node to the key1 node, so it will generate n numbers of key1 nodes to include in the query, and the Detail number of nodes are not constant so I can't generate key2, key3, keyn...., so what I need is to create n numbers of instances of key1 node, but the problem is that when the mapping try to generate the second instance of key1 node it gives an error in the fields that comes from Header structure, maybe I'm trying to solve my problem wrong, could anyone give some advices or direction?, thanks in advance.

Regards,

Julio Cesar

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Thanks Raj for your answer, I'm using the logic you mention, but I have a doubt in this step

Details2(Change context to Header ID)

what do you mean by changing the context, I don't know how to change the context to a node, could you explain more in this step please, thanks again.

Regards,

Julio Cesar

justin_santhanam
Active Contributor
0 Kudos

Julio,

After placing the below logic in the mapping editor

Details2 > SplitByValue> col5

Right click on the Details2 node and choose Context. By Default you will see Details, because Details2 is the child of Details node. I want you to choose the Parent of Details node, I believe that would be HeaderID, am I right?

If you don't want to do that then,

Details2 ---> Remove Context > SplitByvalue> col5 will do the trick!

raj.

Answers (2)

Answers (2)

Former Member
0 Kudos

Thanks Raj, now is working fine, the only adjust I had to do to what you told me is that I had to remove the context of every field mapped with the Header structure and now is generating a key1 node for every detail2 field value in the Detail structure, thanks again and point rewarded, be in touch.

justin_santhanam
Active Contributor
0 Kudos

Julio,

Glad to know,it got solved!

raj.

justin_santhanam
Active Contributor
0 Kudos

Julio,

Are u using the below logic?

Key1 (0..unbounded)

col4 (0..1)

col5 (0..unbounded)

col6( 0..1)

Mapping

Details --> Key1

Header --> col4

Details2(Change context to Header ID) --> SplitByValue ---> col5

Header --> col6

raj.