Five Stars PK
Five Stars

xml create with multiple loop

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.

Capture.PNGdata.PNG

 

<?xml version="1.0" encoding="UTF-8"?>
<commonSmiley FrustratedubscriptionProcessAcknowledge 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>
<commonSmiley Tonguearty 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>
</commonSmiley Tonguearty>
</commonSmiley FrustratedubscriptionProcessAcknowledge>

  • Big Data
  • Data Integration
  • ESB
Tags (1)
14 REPLIES
Six Stars

Re: xml create with multiple loop

I've never used the txmlmap component so I would not be able to help on how to configure it for the looping you want, however i know that the Talend Data Mapper can do what you want (tHMap component).  It has a steep learning curve, but it can pretty much manipulate your data set however you want it.  Do you have access to this component?

Eleven Stars

Re: xml create with multiple loop

If you are using the Enterprise Edition, then use the Talend Data Mapper. If not, this answer I gave on the old forum might help...

https://www.talendforge.org/forum/viewtopic.php?id=54784&_ga=2.48489327.832598656.1502749482-2037499...

Rilhia Solutions
Seven Stars

Re: xml create with multiple loop

Hi,

 

Could you upload an example input file and the expected output XML file ?

 

Eric

Five Stars PK
Five Stars

Re: xml create with multiple loop

Hi Eric,

 

Please find attached inupt file and example xml that we need to generate.

 

 

Thanks 

PK

Seven Stars

Re: xml create with multiple loop

Do you want three output xml files (One per csv line) or a single one (I understodd you wanted to combine lines) ?

Five Stars PK
Five Stars

Re: xml create with multiple loop

Hi Eric,

 

It should be one per input csv lines.

 

Each xml must contain all the entities that are part of that message(messageid) and nested correctly. i.e. if the message has multiple Addresses and each Address has multiple Contacts/Banks, then i need to create a single acknowledgement message(xml) with status for each Address Section and the corresponding Contacts/Banks that are linked to that Address.

 

Hope this makes sense to you.

 

Thanks

PK

Seven Stars

Re: xml create with multiple loop

Hi,

 

I could suggest a solution with data mapper (It should be possible with tXmlMap. You will have to look at URL given by rhall_2_0).

Do you have data mapper (It is included only in platform version not in free version) ?

 

Eric

Five Stars PK
Five Stars

Re: xml create with multiple loop

Hi Eric,

 

I am working on Enterprise edition , so i have data mapper.

 

Can you please provide a solution.

 

Thanks

PK

Seven Stars

Re: xml create with multiple loop

It looks like the xml file you uploaded doesn't match the xlsx file you uploaded.

 

In xslx file, there are three lines with message id is 0386639a-22db-4e15-b032-a213690be59a. And I see three XML files with message id below:

1111-1111-0001

1111-1111-0002

1111-1111-0003

 

Could you upload the expected XML for file for the xslx file you uploaded ?

 

Eric

Five Stars PK
Five Stars

Re: xml create with multiple loop

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>
<commonSmiley Tonguearty 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>
</commonSmiley Tonguearty>
</commonSmiley FrustratedubscriptionProcessAcknowledge>

 

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

Seven Stars

Re: xml create with multiple loop

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:

<comSmiley FrustratedubscriptionProcessAcknowledge 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>
<comSmiley Tonguearty 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>
</comSmiley Tonguearty>
</comSmiley FrustratedubscriptionProcessAcknowledge>

 

Let me know if it meets your requirements.

 

Eric

Five Stars PK
Five Stars

Re: xml create with multiple loop

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

Five Stars PK
Five Stars

Re: xml create with multiple loop

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

Seven Stars

Re: xml create with multiple loop

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