One Star

tFileInputXML - Multi loops for several nodes

Hi everyone,
I have an issue with the use of the component tFileInputXML which I would like to use to fill a database table.
My XML file looks like this :
<?xml version="1.0" encoding="utf-8"?>
<document>
<etab>
<UAI>
<codeEtab>0300026R</codeEtab>
</UAI>
<accueil_Internes>
<origine>0300023M</origine>
<accueil>0300026R</accueil>
<effectif>1</effectif>
<origine>0300026R</origine>
<accueil>0300026R</accueil>
<effectif>153</effectif>
<origine>0300027S</origine>
<accueil>0300026R</accueil>
<effectif>18</effectif>
<origine>0301685U</origine>
<accueil>0300026R</accueil>
<effectif>21</effectif>
</accueil_Internes>
</etab>
</document>
And the schema of my table is like :
UAI char(8), origine char(8), accueil char(8), effectif numeric (the first three fields are my PK).
What I would like to see in my table is four records like :
UAI | origine | accueil | effectif
0300026R | 0300023M | 0300026R | 1
0300026R | 0300026R | 0300026R | 153
0300026R | 0300027S | 0300026R | 18
0300026R | 0301685U | 0300026R | 21
But when I use the component tFileInputXML, I'm only able to retrieve the first line, then it moves on the next etab record.
The job itsefl is simple but I can't find how to do multi-loops in the component for each node (and keeping the same value of UAI, of course...).
Thank for your help !
6 REPLIES
Community Manager

Re: tFileInputXML - Multi loops for several nodes

Hi
It is impossible to achieve this request with only one tFileInputXML, as a workaround, you can use three tFileInputXML components to extract respectively different element, for example, use one tFileInputXML to origine element with xpath loop expression:
/document/etab/accueil_Internes/origine
and then, add a sequence id for each row, and do a join on tMap to merge all columns, for more details, please read my screenshots.

Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tFileInputXML - Multi loops for several nodes

hi ,
if xml file is as below
<?xml version="1.0" encoding="utf-8"?>
<document>
<etab>
<UAI>
<codeEtab>0300026R</codeEtab>
<codeEtab>0300345E</codeEtab>
<codeEtab>03002349T</codeEtab>
</UAI>
<accueil_Internes>
<origine>0300023M</origine>
<accueil>0300026R</accueil>
<effectif>1</effectif>
<origine>0300026R</origine>
<accueil>0300026R</accueil>
<effectif>153</effectif>
<origine>0300027S</origine>
<accueil>0300026R</accueil>
<effectif>18</effectif>
<origine>0301685U</origine>
<accueil>0300026R</accueil>
<effectif>21</effectif>
</accueil_Internes>
</etab>
</document>

how to store the details of it in database table
One Star

Re: tFileInputXML - Multi loops for several nodes

Thank you Shong,

it's like what I've done (I use calculated ids from the xml file to merge my data, more like : "count(./preceding::*)"), but I though there might be something more "elegant"...
The ids' count might work for you Manjuvani.
If your file have a regular structure, you can calculate the id for each element, then use a tMap to merge your data.
But what kind of records do you want to have at the end ?
In my case, this was a bit easier because I kept <codeEtab> as key and I knew that the difference between the element origine and accueil was 1.
So the id for <origine> is "count(./preceding::*)" and for <accueil>, it's like "count(./preceding::*)-1" so they can match in the tMap using these two fields.
Even if it's not a clean solution, it works ! Smiley Happy
One Star

Re: tFileInputXML - Multi loops for several nodes

Thanks for the solution ...
Seven Stars

Re: tFileInputXML - Multi loops for several nodes

@hotline_crlr
You can do it with one tFileInputXML, looping on /document/etab/accueil_Internes//* to get a row for each origine, accueil and effectif. Use the following three expressions for the columns UAI, field and value:
../../UAI/codeEtab
name()
.
Then add a sequence number from 0 to each row and divide by 3 to create a group ID for each set of three values. Then tPivotToColumnsDelimited for your final result as a file, which you can insert into your DB.
One Star

Re: tFileInputXML - Multi loops for several nodes

hi alvey,
can u describe the job with screen shots please.......