topic Re: [resolved] Output XML multi loop from unique table in Talend Studio

[resolved] Output XML multi loop from unique table

Anonymous — Tue, 23 Sep 2014 12:26:05 GMT

Hello,
I would like to generate an XML file with multi loops. The source is just one table.
Here's my table structure :
Student
Name | Birth | Sex | Driving_licence | Sport1 | Sport2 | Sport3 | Sport4 | Sport5 | Activity1 | Activity2 | Activity3 | Activity4 | Activity5
And the XML structure :
<SCHOOL>
<STUDENTS>
    <STUDENT>
      <NAME>JOHN DOE</NAME>
      <BIRTH>13-07-1983</NAISSANCE>
      <SEX>M</SEX>
      <SPORTS>
        <SPORT>BaseBall</SPORT>
        <SPORT>Soccer</SPORT>
    <SPORT>Tennis</SPORT>
      </SPORTS>
      <ACTIVITIES>
        <ACTIVITY>Library</ACTIVITY>
        <ACTIVITY>Cooking</ACTIVITY>
      </ACTIVITIES>
      <DRIVING_LICENCE>8</DRIVING_LICENCE>
    </STUDENT>
    <STUDENT>
      <NAME>BARBARA SHU</NAME>
      <BIRTH>20-02-1987</NAISSANCE>
      <SEX>F</SEX>
      <SPORTS>
        <SPORT>Danse</SPORT>
    <SPORT>Tennis</SPORT>
      </SPORTS>
      <ACTIVITIES>
      </ACTIVITIES>
      <DRIVING_LICENCE>8</DRIVING_LICENCE>
    </STUDENT>
</STUDENTS>
</SCHOOL>
I try many solutions but there are not very nice. I have difficulties to apply multi loops in my outputXML. Can you help me ?
Thanks !!
Arnaud

Re: [resolved] Output XML multi loop from unique table

Anonymous — Tue, 23 Sep 2014 13:46:15 GMT

Hi Amaud,
I'm sure you would be trying this using tWriteXMLField component.
My suggestion would be rather than going for a one-time XML creation, go for the below approach:
1. Create & populate all the independent, non-looping elements first; starting from the outer-most element & working your way in.
2. Using separate ETL flow/pipelines create place-holders for all looping elements & populate accordingly
3. ITERATE the above steps for the outer-most looping element i.e. in your case STUDENT. (From the XML structure given, I assume element SCHOOL is not repeating.)
I'm also assuming that normalizing the data is not currently the challenge under discussion.
Hope it helps.
MathurM

Re: [resolved] Output XML multi loop from unique table

Anonymous — Tue, 23 Sep 2014 14:20:58 GMT

Hello mathurm,
I don't understand your approach.
tWriteXMLField read an XML file to populate a table.
My problem is :
student table -> ??? -> XML output
Else I don't well use tWriteXMLField ??
Thanks,
Arnaud

Re: [resolved] Output XML multi loop from unique table

Anonymous — Thu, 25 Sep 2014 10:39:10 GMT

Hi Arnaud,
Let me be a bit more clearer....
What I intended to say was that instead of creating the complete XML structure in one-go, you should build it part-by-part (SINCE THERE ARE MULTIPLE LOOPING ELEMENTS, & no components support that.)
Also, component tWriteXMLField is employed to write/create an XML structure only.
Try the below approach:
1. Extract STUDENT records iteratively, like
Student table input --> tFlowtoIterate --> tJava (which can link to further sub-jobs on 'onComponentOK' trigger), to process each row one-by-one
2. Then, you can build the following structure using a tWriteXMLField component:
<SCHOOL>
<STUDENTS>
<STUDENT>
<NAME>JOHN DOE</NAME>
<BIRTH>13-07-1983</NAISSANCE>
<SEX>M</SEX>
<DRIVING_LICENCE>8</DRIVING_LICENCE>
</STUDENT>
</STUDENTS>
</SCHOOL>
This can be achieved by extracting required fields & populating them in appropriate place-holders in the tWriteXMLField component. (There won't be any need for normalizing this data) Looping element in tWriteXMLField would be '<STUDENT>'.
Then, save the thus created XML in globalmap, using XMLutils commands.
3. Then, using 2 different pipelines you need to extract the 'SPORTS' & 'ACTIVITIES' data, normalize it, create XML structure like below using tWriteXMLField component & populate it
SPORTS>
<SPORT>BaseBall</SPORT>
<SPORT>Soccer</SPORT>
<SPORT>Tennis</SPORT>
</SPORTS>
<ACTIVITIES>
<ACTIVITY>Library</ACTIVITY>
<ACTIVITY>Cooking</ACTIVITY>
</ACTIVITIES>
After creation of each of the above XMLs, merge the SPORTS xml into SCHOOL xml using XMLutils commands.
4. The above process will iterate for each incoming STUDENT record.
5. Once the complete XML is ready, we can then write it to a file using tFileOutputXML or tAdvancedFileOutputXML components, based on your need.
The above is a high-level outlined approach. you may need to fill up the gaps at a couple of places.
MathurM