[resolved] DeNormalized data to XML message

[Anonymous]

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>

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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 ?

[Anonymous · Accepted solution]

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

Regards
Shong

[Anonymous]

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

[Anonymous]

Great, thanks for your feedback.