xml create with multiple loop

[Anonymous]

Hi,

I have requirement where i need to create acknowledgement xml file and below is the logic:

Ack message should be nested in a way that , if the message has multiple Addresses and each Address has multiple Contacts/Banks, then i need to send a single acknowledgement message with status for each Address Section and the corresponding Contacts/Banks that are linked to that Address. Data example is below.

-->I have tried to use txmlmap component but it seems grouping is not working correctly. I have attached xml file which i am getting as a result but it is not in correct format as described in logic.

---> for duplicate address id <common:address id="S00195003"> it should grouped in one tag, can someone help me to achieve this.

<?xml version="1.0" encoding="UTF-8"?>
<commonubscriptionProcessAcknowledge xmlns:common="http://common.subscription.partyconnect.ge.com">
<common:messageId>0386639a-22db-4e15-b032-a213690be59a</common:messageId>
<common:transactionId>517560</common:transactionId>
<common:externalTransactionId/>
<common:downstreamOrgId>9792</common:downstreamOrgId>
<common:delivered>True</common:delivered>
<common:status>FAILURE</common:status>
<common:message>Data not saved in ERP</common:message>
<commonarty id="S00195">
<common:status>SUCCESS</common:status>
<common:message/>
<common:downstreamEntityId>10142</common:downstreamEntityId>
<common:address id="S00195003">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
<common:bankAccount id="BA-0000-0000-0000-0001">
<common:status>SUCCESS</common:status>
<common:message/>
<common:downstreamEntityId>11111/bank01</common:downstreamEntityId>
</common:bankAccount>
<common:contact id="CO-0000-0000-0000-0622">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
</common:contact>
</common:address>
<common:address id="S00195003">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
<common:bankAccount id="BA-0000-0000-0000-0002">
<common:status>SUCCESS</common:status>
<common:message/>
<common:downstreamEntityId>11111/bank02</common:downstreamEntityId>
</common:bankAccount>
<common:contact id="CO-0000-0000-0000-0624">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
</common:contact>
</common:address>
<common:address id="S00195004">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
<common:bankAccount id="BA-0000-0000-0000-0003">
<common:status>SUCCESS</common:status>
<common:message/>
<common:downstreamEntityId>11111/bank03</common:downstreamEntityId>
</common:bankAccount>
<common:contact id="CO-0000-0000-0000-0625">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
</common:contact>
</common:address>
</commonarty>
</commonubscriptionProcessAcknowledge>

[Anonymous]

Ohh, xml  file i attached is just a format of xml,i will attached output which i am getting.

xmlns:common="http://common.subscription.partyconnect.ge.com">
<common:messageId>0386639a-22db-4e15-b032-a213690be59a</common:messageId>
<common:transactionId>517560</common:transactionId>
<common:externalTransactionId/>
<common:downstreamOrgId>9792</common:downstreamOrgId>
<common:delivered>True</common:delivered>
<common:status>FAILURE</common:status>
<common:message>Data not saved in ERP</common:message>
<commonarty id="S00195">
<common:status>SUCCESS</common:status>
<common:message/>
<common:downstreamEntityId>10142</common:downstreamEntityId>
<common:address id="S00195003">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
<common:bankAccount id="BA-0000-0000-0000-0001">
<common:status>SUCCESS</common:status>
<common:message/>
<common:downstreamEntityId>11111/bank01</common:downstreamEntityId>
</common:bankAccount>
<common:contact id="CO-0000-0000-0000-0622">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
</common:contact>
</common:address>
<common:address id="S00195003">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
<common:bankAccount id="BA-0000-0000-0000-0002">
<common:status>SUCCESS</common:status>
<common:message/>
<common:downstreamEntityId>11111/bank02</common:downstreamEntityId>
</common:bankAccount>
<common:contact id="CO-0000-0000-0000-0624">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
</common:contact>
</common:address>
<common:address id="S00195004">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
<common:bankAccount id="BA-0000-0000-0000-0003">
<common:status>SUCCESS</common:status>
<common:message/>
<common:downstreamEntityId>11111/bank03</common:downstreamEntityId>
</common:bankAccount>
<common:contact id="CO-0000-0000-0000-0625">
<common:status>FAILURE</common:status>
<common:message/>
<common:downstreamEntityId/>
</common:contact>
</common:address>
</commonarty>
</commonubscriptionProcessAcknowledge>

In thix xml address id (<common:address id="S00195003">) is repeating, but it should not repeat.

[Anonymous]

Please find enclosed a suggestion.

There are two maps:

1-ACK_CSV_2_XML: It translates CSV to XML. But you get duplicates. So it calls a second map (In properties of the map there is a "Next map" field

2-ACK_XML_FINAL: it removes the duplicates

TDM doesn't handle excel format. You will first have to transform your excel file into csv file before calling tHmap.

My test input file was:

messageId;transactionId;subsciptionId;downstreamSystem_orgId;Delivered;Status;Message;PartyId;Status1;DownStreamEntityId;AddressID;Status2;DownStreamEntityId;bankAccount;Status3;DownStreamEntityId;ContactID;Status3;DownStreamEntityId
0386639a-22db-4e15-b032-a213690be59a;517560;517560;9792;True;FAILURE;Data not saved in ERP;S00195;SUCCESS;10142;S00195003;FAILURE;;BA-0000-0000-0000-0001;SUCCESS;11111/bank01;CO-0000-0000-0000-0622;FAILURE;
0386639a-22db-4e15-b032-a213690be59a;517560;517560;9792;True;FAILURE;Data not saved in ERP;S00195;SUCCESS;10142;S00195003;FAILURE;;BA-0000-0000-0000-0002;SUCCESS;11111/bank02;CO-0000-0000-0000-0624;FAILURE;
0386639a-22db-4e15-b032-a213690be59a;517560;517560;9792;True;FAILURE;Data not saved in ERP;S00195;SUCCESS;10142;S00195004;FAILURE;;BA-0000-0000-0000-0003;SUCCESS;11111/bank03;CO-0000-0000-0000-0625;FAILURE;

The output file was:

<comubscriptionProcessAcknowledge xmlns:com="http://common.subscription.partyconnect.ge.com">
<com:messageId>0386639a-22db-4e15-b032-a213690be59a</com:messageId>
<com:transactionId>517560</com:transactionId>
<com:externalTransactionId>517560</com:externalTransactionId>
<com:downstreamOrgId>9792</com:downstreamOrgId>
<com:delivered>True</com:delivered>
<com:status>FAILURE</com:status>
<com:message>Data not saved in ERP</com:message>
<comarty id="S00195">
<com:status>SUCCESS</com:status>
<com:downstreamEntityId>10142</com:downstreamEntityId>
<com:address id="S00195003">
<com:status>FAILURE</com:status>
<com:downstreamEntityId></com:downstreamEntityId>
<com:bankAccount>
<com:status>SUCCESS</com:status>
<com:downstreamEntityId>11111/bank01</com:downstreamEntityId>
</com:bankAccount>
<com:bankAccount>
<com:status>SUCCESS</com:status>
<com:downstreamEntityId>11111/bank02</com:downstreamEntityId>
</com:bankAccount>
<com:contact id="CO-0000-0000-0000-0622">
<com:status>FAILURE</com:status>
<com:downstreamEntityId></com:downstreamEntityId>
</com:contact>
<com:contact id="CO-0000-0000-0000-0624">
<com:status>FAILURE</com:status>
<com:downstreamEntityId></com:downstreamEntityId>
</com:contact>
</com:address>
<com:address id="S00195004">
<com:status>FAILURE</com:status>
<com:downstreamEntityId></com:downstreamEntityId>
<com:bankAccount>
<com:status>SUCCESS</com:status>
<com:downstreamEntityId>11111/bank03</com:downstreamEntityId>
</com:bankAccount>
<com:contact id="CO-0000-0000-0000-0625">
<com:status>FAILURE</com:status>
<com:downstreamEntityId></com:downstreamEntityId>
</com:contact>
</com:address>
</comarty>
</comubscriptionProcessAcknowledge>

Let me know if it meets your requirements.

Eric

ACK.zip

[Anonymous]

Hi Eric,

It looks correct except there is bank account is missing in xml, but rest is looking correct.

Also can you please send me screenshot of mapping or steps you did in tHMap component,i have never used it before.

Thanks  a lot for your help.

Thanks

PK

[Anonymous]

Hi Eric,

Thanks for your help.

I can not import the file because i am working on older version of talend. Can you please share the screenshot of mapping in thmap 

Thanks

PK

[Anonymous]

Hi,

I'm sorry. There would be too many steps to explain with screenshots so that you are able to reproduce these two maps.

Eric

[pgosavi]

Hi,

I am facing similar issue of trying to get multiple loop in the xml output file. Our input are multiple database queries. Do you know if Talend Data mapper can use database queries as input. I tried to download you solution zip file, however, your solution was developed using a newer version, therefore cannot import it into out talend 6.1 version.

Attached is the output structure with multiple loops that we need. 

So far I have tried to use multiple tAdvancedFileOutputXML for each loop and append them. however we also have a requirement, that even if a particular section/loop is empty, we still need to have the place holders for empty loops. Also, I have to define every loop which is very cumbersome

Could you please suggest any other way?

Capture1.JPG

[Anonymous]

Hi,

You can query your database in your job and use the result as input of your thmap component.

Eric

[Rajender]

Hi Eric

Thanks for providing create xml with multiple loop.

It was a great help to understand Talend Data mapper. I have created multiple loops xml with duplicates. Part 1 as per your attachment. But how to remove duplicate is not much clear.

Can you give details how remove of duplicates code/behave.

[Anonymous]

Hi,

See example enclosed.

Eric

duplicate.png

[Rajender]

Hi Eric

Thanks for quick reply and answer. 

I am able to resolve duplicates at level one. There are duplicates exists at level 3(nested loop level 3). I tried same approach it resolve the duplicates. I am analyzing with different data.

Much appreciated your help.   

Regards

Rajender Prasad