Four Stars

[resolved] DeNormalized data to XML message

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>
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: [resolved] DeNormalized data to XML message

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

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
7 REPLIES
Community Manager

Re: [resolved] DeNormalized data to XML message

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: [resolved] DeNormalized data to XML message

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.
Community Manager

Re: [resolved] DeNormalized data to XML message

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.
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: [resolved] DeNormalized data to XML message

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 ?
Community Manager

Re: [resolved] DeNormalized data to XML message

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

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: [resolved] DeNormalized data to XML message

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

Re: [resolved] DeNormalized data to XML message

Great, thanks for your feedback.
----------------------------------------------------------
Talend | Data Agility for Modern Business