topic Re: xml create with multiple loop in Talend Studio

xml create with multiple loop

Anonymous — Sat, 16 Nov 2024 09:24:47 GMT

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>

Re: xml create with multiple loop

mayo — Mon, 14 Aug 2017 18:06:29 GMT

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?

Re: xml create with multiple loop

Anonymous — Mon, 14 Aug 2017 23:35:30 GMT

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-2037499045.1489361547

Re: xml create with multiple loop

Anonymous — Tue, 15 Aug 2017 07:04:48 GMT

Hi,

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

Eric

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 05:30:03 GMT

Hi Eric,

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

Thanks

Bulk_Upload_Acknowledgment_Latest_TEST.xlsx
sc-pubsub-new-supplier-ack-async-bulk.xml

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 06:03:46 GMT

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

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 07:36:24 GMT

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

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 09:48:10 GMT

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

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 12:39:56 GMT

Hi Eric,

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

Can you please provide a solution.

Thanks

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 13:36:09 GMT

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

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 13:41:10 GMT

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.

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 14:56:04 GMT

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

Re: xml create with multiple loop

Anonymous — Wed, 16 Aug 2017 16:14:21 GMT

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

Re: xml create with multiple loop

Anonymous — Thu, 17 Aug 2017 05:10:58 GMT

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

Re: xml create with multiple loop

Anonymous — Thu, 17 Aug 2017 07:00:25 GMT

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

Re: xml create with multiple loop

pgosavi — Fri, 26 Jan 2018 20:39:58 GMT

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

Re: xml create with multiple loop

Anonymous — Sat, 27 Jan 2018 05:53:01 GMT

Hi,

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

Eric

Re: xml create with multiple loop

Rajender — Mon, 15 Oct 2018 13:30:51 GMT

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.

Re: xml create with multiple loop

Anonymous — Mon, 15 Oct 2018 14:34:19 GMT

Hi,

See example enclosed.

Eric

duplicate.png

Re: xml create with multiple loop

Rajender — Mon, 15 Oct 2018 19:16:39 GMT

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