topic tXMLMap - Complex output with multiple loops in Talend Studio

tXMLMap - Complex output with multiple loops

Anonymous — Tue, 07 Oct 2014 02:08:48 GMT

Ok - I don't think what I am trying to do is terribly complex, but I have read all of the documentation and searched the forum for hours and am still unable to accomplish what I am trying to do. All of the examples are either too simple or a little different from my scenario (which I would think is fairly common).
I would like to output an XML document containing customer data, order data, and phone number data. The relationship is like this:
1. The document can contain many customers.
2. Each customer can have many orders.
3. Each customer can have many phone numbers.
A sample file of my desired output is attached.
My input contains data from 3 different sources which are being joined together in the Map:
Customers:
ID First Last Primary Phone
1 John Smith 888-555-1212
2 Jane Smith 888-555-1212
3 Bob Evans 444-555-8888
4 Gina Evans 444-555-8888
5 Sallie Mae 111-222-3333
6 John Cueto 888-555-1212
Phone Numbers:
ID Sort Type Phone Number
1 1 HOME 800-555-1212
1 2 WORK 800-555-1212
1 3 MOBILE 800-555-1212
1 4 FAX 800-555-1212
2 1 HOME 800-555-1212
2 2 WORK 800-555-1212
2 3 MOBILE 800-555-1212
3 1 HOME 800-555-1212
3 2 WORK 800-555-1212
3 3 MOBILE 800-555-1212
3 4 FAX 800-555-1212
4 1 HOME 800-555-1212
4 2 WORK 800-555-1212
5 1 HOME 800-555-1212
5 2 WORK 800-555-1212
5 3 MOBILE 800-555-1212
5 4 FAX 800-555-1212
5 5 OTHER 800-555-1212
Orders:
ID Seq. OrderNumber OrderDate
1 1234 ORDER1234 9/29/2014
1 1235 ORDER1235 9/29/2014
1 1236 ORDER1236 9/28/2014
1 1237 ORDER1237 9/27/2014
2 1238 ORDER1238 9/26/2014
2 1239 ORDER1239 9/25/2014
2 1240 ORDER1240 9/24/2014
3 1241 ORDER1241 9/23/2014
3 1242 ORDER1242 9/22/2014
3 1243 ORDER1243 9/21/2014
3 1244 ORDER1244 9/20/2014
4 1245 ORDER1245 9/19/2014
4 1246 ORDER1246 9/18/2014
5 1247 ORDER1247 9/17/2014
5 1248 ORDER1248 9/16/2014
5 1249 ORDER1249 9/15/2014
5 1250 ORDER1250 9/14/2014
5 1251 ORDER1251 9/13/2014
I am trying to join these 3 in the tXMLMap, and getting various results - none of which match.
I have been trying to set orders and contact_methods as loop elements (2 loop elements), but when I do that I lose the ability to group.
Can anyone explain to me how to set up this tXMLMap to output the desired format? All of the examples I can find which contain multiple loop elements are for outputting non-hierarchical data (i.e. manufacturers and orders, but not related in a hierarchy). I can't find any examples which show how to output data like the attached file - where there is a hierarchy, and the multiple loop elements occur within a sub-element.

xmlsample1.zip.zip

Re: tXMLMap - Complex output with multiple loops

Anonymous — Wed, 08 Oct 2014 07:26:27 GMT

At first I would not use tXMLMap instead using a normal tMap to build the plain output (join result). This is by far easier to be done and you can check the result much better.
Next after validating the join result I would format the result in the target format XML and this can be done with the tFileOutputXML or similar.

Re: tXMLMap - Complex output with multiple loops

Anonymous — Fri, 10 Oct 2014 15:42:53 GMT

jlolling:
I have simplified my program to illustrate the issue as you suggested. I used a standard tMap component to perform the joins, and out put the results to an Excel file. I made sure to select "All Matches" for the join type for each join because there are multiple results for each join. Next I created a simple job which reads that data from Excel, and converts it to XML. This removes all complexity from the job and isolates the problem I'm having with creating the XML output.
I have attached a .zip file which contains the following:

source_data.xls - this is the source data which is to be converted into XML. Keep in mind that this file contains multiple records for each customer and there is repetition because the file contains information on un-related entities. For example, if a customer has 3 different phone numbers, and has placed 2 orders, then there will be 6 records in this file (2x3=6) for that customer.
xmlsample.xml - this is a sample of how I want my output formatted. I want to have one element for a single customer (identified by the "id" tag), and inside this element I want to have multiple orders and multiple phone numbers displayed independently of each other.
getCustomers_test.zip - this is the Talend job I exported to actually show you how I am performing the XML operations. I tried it two different ways, and neither way yields the desired results. Actually I have tried hundreds of different configurations but I am only including 2 here. This job was created using TOS ESB 5.4.1.
customers_fromAdvancedXMLOutput.xml - this is the XML that was produced using the tAdvancedXMLFileOutput component. Obviously this does not match the xmlsample.xls format which I what I am aiming for.
customers_fromXMLMap.xml - this is the XML that was produced using the tXMLMap component. It does not match the required format either.

So my question is whether anyone can show me what I am doing wrong. How can I take the data in the sample .xls file and convert it into the desired XML format? It seems to me that the tXMLMap component is the correct component to use because this component is the only one that supports multiple loop elements.
Any help that anyone can provide will be appreciated.
Thanks.

sampleProgram.zip.zip

Re: tXMLMap - Complex output with multiple loops

Anonymous — Tue, 21 Jun 2016 08:19:29 GMT

hello jbaird123
Same type of problem which you are facing is there for my talend job.
I am also not able to find any solution.
Please can anyone open this issue again and share some knowledge.
I want multiple loop in txmlmap. So that I can get proper json in my REST response.

Re: tXMLMap - Complex output with multiple loops

Anonymous — Tue, 21 Jun 2016 11:32:25 GMT

Hi SumeetAwari: I had the same problem in various customer situations.
Because of that, I give up using the tXMLMap for such cases.
The problems are: limited loop capabilities and performance leaks.
I have create a complete new json component suite which follows a complete different approach. Instead of trying to build the json document in one huge step (tXMLMap) I prefer to build the json documents in sub jobs level by level.
Perhaps you consider using these components:
https://exchange.talend.com/#marketplaceproductoverview:gallery=marketplace%252F1&pi=marketplace%252F1%252Fproducts%252F1438%252Fitems%252F2492
With these components I was able to build unlimited complex documents and the memory leaks are also gone.

Re: tXMLMap - Complex output with multiple loops

Anonymous — Tue, 21 Jun 2016 11:43:53 GMT

I've found that it can be much better to simply do it in Java using something like XStream ( http://x-stream.github.io/) rather than hacking around with Talend components to do this. All it takes is a couple of routines (a class representing your XML and a class to handle your XStream processing) and you can use anywhere in your job. However I understand that not everybody likes Java.
Another way I have approached this can be seen on this Talendforge question....
https://community.talend.com/t5/Design-and-Development/How-to-generate-XML-with-multi-loop/td-p/104652

Re: tXMLMap - Complex output with multiple loops

Anonymous — Tue, 21 Jun 2016 11:49:48 GMT

Hi rhall_2.0: The topic last post topic was json documents. And I have a couple of customers which do not want to write as much Java code in the jobs. But thanks a lot for the idea related to XML. Currently we use JAX-B to work with complex XML documents.

Re: tXMLMap - Complex output with multiple loops

Anonymous — Tue, 21 Jun 2016 12:24:40 GMT

hello jlolling
But I m not able to understand that, Why Talend is not providing multiple loops in one txmlmap??
What is the reason behind that??

Re: tXMLMap - Complex output with multiple loops

Anonymous — Tue, 21 Jun 2016 12:42:11 GMT

JLolling: oops, I assumed that you were talking about building the data structure using JSON (because of your set of components) and then converting to XML once the document is done.
SumeetAwari: I believe the reason that Talend only support one loop is that it is actually an incredibly complex task to provide functionality in 1 component to build *ANY* schema in one go. You have to consider XML as a mini relational database.

Re: tXMLMap - Complex output with multiple loops

Anonymous — Tue, 21 Jun 2016 12:49:11 GMT

then what is final solution we should follow for web services

Re: tXMLMap - Complex output with multiple loops

Anonymous — Tue, 21 Jun 2016 12:57:18 GMT

Hi rhall.2.0: No I am working directly with json and do not need in my current projects xml at all.
So the native json processing is exactly what I need.
The tXMLMap supports multiple loops but it is impossible to loop inside a loop multiple times.
Actually it would not be a big deal but Talend joins all input flows to one flow and do not use the input flows separately.

Re: tXMLMap - Complex output with multiple loops

Anonymous — Fri, 24 Jun 2016 15:06:27 GMT

Hi jlolling
I am using dat component as u specified for generating complex json
But I have a problem in generating json with structure of array object inside array object for example
{"response": {"code": "00","status": "OK","content": {"Students": },{"sid": "3","name": "C","Marks": },{"sid": "4","name": "D","Marks": {"marks": "56","sub": "cs"}},"error": ""}}
I want such structure of json
please suggest some method with that component for this structure
thanks in advance