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 !
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.
---------------------------------------------------------- Talend | Data Agility for Modern Business
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 !
@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.