Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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 !
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
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
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 !
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 !
