Outputting XML with Denormalized Data in One Row

One Star

Outputting XML with Denormalized Data in One Row

Hi,
I am confused as to whether the following is possible in Talend Open Studio...
I have a denormalized data set where each row has something similar to:
game_id: 5
game_name: game_name
genre_id: 7
ganre_name: genre_name
tag_id: 1,2,3
tag_name: tag_name1,tag_name2,tag_name3
brand_name:brand1,brand2,brand3,brand4
Is it possible to split the denormalized fields into multiple nodes within one level in an XML output?
<row>
<game_id>5</game_id>
<game_name>game_name</game_name>
<genre_id>7</genre_id>
<genre_name>genre_name</genre_name>
<tag_id>1</tag_id>
<tag_id>2</tag_id>
<tag_id>3</tag_id>
<tag_name>tag_name1</tag_name>
<tag_name>tag_name2</tag_name>
<tag_name>tag_name3</tag_name>
<brand_name>brand1</brand_name>
<brand_name>brand2</brand_name>
<brand_name>brand3</brand_name>
<brand_name>brand4</brand_name>
</row>
The total number of items in any of the denormalized fields is dynamic. There is always 1 or more of tag_id, tag_name, and brand_name, but each row in the data set has its own limit. I don't know if there are 3 or 4 or 10 ahead of time. Tag_id and tag_name correspond to each other so will have the same number of items per row, but brand_name is independent of those two fields.
It seems tAdvancedFileOutputXML does not take more than one loop element. Please advise ASAP.
Best regards,
LC
Community Manager

Re: Outputting XML with Denormalized Data in One Row

Hi
Yes, the tAdvancedFileOutputXML does not take more than one loop elemen at a time, however, you can use several tAdvancedFileOutputXML for each loop element with 'append the source xml file' option. see http://www.talendforge.org/forum/viewtopic.php?id=5975
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Outputting XML with Denormalized Data in One Row

Hi,
The append option doesn't seem to give me what I need. It does not seem to keep track of which looped values go with which rows. The number of values looped through is different for each field and different for each row.

Given the SAMPLE INPUT
ROW1 with
game_id 1
genre_id 7
tag_id 1,2,3
tag_name tag1,tag2,tag3
brand_name brand1, brand2
ROW2 with
game_id 2
genre_id 8
tag_id 1,4
tag_name tag1,tag4
brand_name brand1,brand3,brand5

DESIRED XML OUTPUT should be
<add>
<doc>
<field name="game_id">1</field>
<field name="genre_id">7</field>
<field name="tag_id">1</field>
<field name="tag_id">2</field>
<field name="tag_id">3</field>
<field name="tag_name">tag1</field>
<field name="tag_name">tag2</field>
<field name="tag_name">tag3</field>
<field name="brand_name">brand1</field>
<field name="brand_name">brand2</field>
</doc>

<doc>
<field name="game_id">2</field>
<field name="genre_id">8</field>
<field name="tag_id">1</field>
<field name="tag_id">4</field>
<field name="tag_name">tag1</field>
<field name="tag_name">tag4</field>
<field name="brand_name">brand1</field>
<field name="brand_name">brand3</field>
<field name="brand_name">brand5</field>
</doc>
</add>

Is this not possible in Talend?

Best regards,
LC