tFileInputXML - Multi loops for several nodes

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

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch