topic Re: tFileInputXML - Multi loops for several nodes in Talend Studio

tFileInputXML - Multi loops for several nodes

Anonymous — Thu, 23 May 2013 11:33:37 GMT

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 !

Re: tFileInputXML - Multi loops for several nodes

Anonymous — Fri, 24 May 2013 14:54:14 GMT

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

Re: tFileInputXML - Multi loops for several nodes

_AnonymousUser — Wed, 29 May 2013 12:04:41 GMT

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

Re: tFileInputXML - Multi loops for several nodes

Anonymous — Wed, 29 May 2013 17:00:39 GMT

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 !

Re: tFileInputXML - Multi loops for several nodes

_AnonymousUser — Thu, 30 May 2013 05:57:18 GMT

Thanks for the solution ...

Re: tFileInputXML - Multi loops for several nodes

alevy — Thu, 30 May 2013 09:21:44 GMT

@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.

Re: tFileInputXML - Multi loops for several nodes

_AnonymousUser — Mon, 03 Jun 2013 05:48:40 GMT

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