topic Re: [resolved] DeNormalized data to XML message in Talend Studio

[resolved] DeNormalized data to XML message

Anonymous — Tue, 20 Dec 2016 16:55:20 GMT

Hello,
I am having issue to create xml message (to deliver to Queue) with denormalized data (salary details will be loop). i am able to create full xml file but i want to create a message for each employee id. can you please help ?
Input Data:
employee_id|month|salary
1|jan;feb;mar|1000;2000;3000
2|jan;feb|5000;6000
Expected Output:
xml msg 1:
<org>
<employee details>
<employee_id>1</employee_id>
</employee details>

<salary details>
<month>jan</month>
<salary>1000</salary>
</salary details>
<salary details>
<month>feb</month>
<salary>2000</salary>
</salary details>

<salary details>
<month>mar</month>
<salary>3000</salary>
</salary details>
</org>

xml msg 2:
<org>
<employee details>
<employee_id>2</employee_id>
</employee details>

<salary details>
<month>jan</month>
<salary>5000</salary>
</salary details>
<salary details>
<month>feb</month>
<salary>6000</salary>
</salary details>
</org>

Re: [resolved] DeNormalized data to XML message

Anonymous — Wed, 21 Dec 2016 07:49:45 GMT

Hello
You need to iterate each row of input data and design the jobs as below, for example
main job:
tfileinputDelimited--main--tFlowToIterate--iterate--tRunJOb
tRunjob: call the child job, and pass current data flow to child job, refers to this article about how pass value from main job to child job.
child job:

In child job, normalize the month and salary separately, cache the temporary result in memory, read them back from memory and do a join to merge the columns, the output will be:

Starting job test3 at 15:50 21/12/2016.
 connecting to socket on port 3464
 connected
.-----------+--+-----+------.
|         tLogRow_1         |
|=----------+--+-----+-----=|
|sequence_id|id|month|salary|
|=----------+--+-----+-----=|
|1          |1 |jan  |1000  |
|2          |1 |feb  |2000  |
|3          |1 |mar  |3000  |
'-----------+--+-----+------'
 disconnected
Job test3 ended at 15:50 21/12/2016.

For more details, please see
mainJob.zip
Regards
Shong

Re: [resolved] DeNormalized data to XML message

Anonymous — Wed, 21 Dec 2016 14:48:07 GMT

Shong, Thanks for the quick response. The real difficult part i have is to create the message with data loop (salary_details) after normalization. if i use twritexmlfield component, each message is split into multiple records (each record for salary details).
I want create 1 message for each input denormalize record. This message should contain the looping of salary details (1 output record for each of input record).
I am able to create xml file using tadvancedoutputxml component then read the file using </org> as line terminator and remove unwanted text but this may not be ideal solution when we have high volume.
any help is greatly appreciated.

Re: [resolved] DeNormalized data to XML message

Anonymous — Fri, 23 Dec 2016 10:17:09 GMT

Hello
In my scenario, you just need to use a tWriteXMLFields component replace of tLogRow, set id column as group element, it will generate a xml message for each input data.

Re: [resolved] DeNormalized data to XML message

Anonymous — Fri, 23 Dec 2016 17:52:30 GMT

Shang, it did't work. in my example, i have 2 denormalized records (5 after normalization). I am expecting 2 output records from tWriteXMlFields but i am getting 5 records. Below is my xml map (can't setup group element without loop element). can you please help ?

Re: [resolved] DeNormalized data to XML message

Anonymous — Mon, 26 Dec 2016 03:02:25 GMT

Hi
Have you imported my job and tested? It is able to generate only 2 XML messages.

Regards
Shong

Re: [resolved] DeNormalized data to XML message

Anonymous — Tue, 27 Dec 2016 13:36:07 GMT

Thanks Shong. it's working now. appreciate your help.

Re: [resolved] DeNormalized data to XML message

Anonymous — Wed, 28 Dec 2016 04:53:00 GMT

Great, thanks for your feedback.