Hi,
I'd like to be able to build an xml file with a nested multiple loop. Output XML example is below:
<System>
<Art name = "Uni2">
<Titles id="1">
<Title> Cave <Title>
<Title> Mogoku <Title>
<Title> Dunhuang <Title>
<Title> Mogao <Title>
</Titles>
<Types>
<Type> Painting <Type>
<Type> Acrylic and ink on paper <Type>
<Type> Photography <Type>
</Types>
<Cultures>
<Culture> Greek <Culture>
<Culture> Mediterranean <Culture>
</Cultures>
</Art>
<Art name="Uni1">
<Titles id="2">
<Title> Head <Title>
<Title> Athena <Title>
</Titles>
<Types>
<Type> Drawings <Type>
<Type> Watercolors <Type>
</Types>
<Cultures>
<Culture> Mogul <Culture>
<Culture> Lahore <Culture>
<Culture> Pakistan <Culture>
</Cultures>
</Art>
</System>
Input of transformation process is four different tables of each art, titles, types, and cultures. All four tables can be join using id. Example of one table is:
__________________
|ID | Title |
|1 | Cave |
|1 | Magoku |
|1 | Dunhang|
|1 | Magao |
|2 | Head |
|2 | Athena |
__________________
Other both tables are also like above one.
I am facing problem in specifing loops in XML output. I have tried all three xml file outputs: tfileoutputxml, tadvancedfileoutputxml, and tfileoutputmsxml.
Any tip on that ?
Do you have Talend Enterprise Edition or the Open Source Edition? If it is the Enterprise Edition try using the Talend Data Mapper. It will handle this easily.
If you only have the Open Source Edition the easiest way to achieve this is to build "mini XML" docs for your nested loops and concatenate them with your outer loops. You can only created one looping element with the tXMLMap component. This will mean creating each looping "mini xml" with a key column to enable you to join it to the outer xml document. The easiest way to do this is to create your "mini xmls" as a data set and when you get round to building the outer document, place your key values where you want your "mini xmls". Once the outer document is done, convert it and the "mini xmls" to Strings (removing the xml headers from the "mini xmls") and use a String find and replace mechanism to replace your key values with your "mini xml" loops.
It sounds complicated, but is actually quite easy
I have downloaded Talend Data Integration Enterprise Edition (trial version). But I am unable to find Talend Data Mapper in it. Can you help me to find Talend Data Mapper in it.
Start by looking for the tHMap component. Then you will need to Google Talend Data Mapper to figure it out. It's not easy to describe and not easy to absorb the information provided online. Trial and error is the best approach to this.
I have transformed CSV files into mini XMLs. An example of title XML after transformation is:
<Titlesfull>
<Titlessub>
<id> 1 </id>
<Titles>
<Title> "Cave"</Title>
<Title> "Magoku"</Title>
<Title> "Dunhang"</Title>
<Title> "Magao"</Title>
</Titles>
</Titlessub>
<Titlessub>
<id> 2 </id>
<Titles>
<Title> "Head"</Title>
<Title> "Athena"</Title>
</Titles>
</Titlessub>
</Titlesfull>
But I am unable to use a String find and replace mechanism to replace my key values with my "mini XML" loops. Even I am unable to parse "mini XML" strings data as an input format (to direct input into tMap). Moreover, I am also failing to parse XML file into below table so that I can replace key values with "titles" block.
Titles | ID |
___________________________|_____|
</Titles> |
<Title> "Cave"</Title> | |
<Title> "Magoku"</Title> | |
<Title> "Dunhang"</Title> | 1 |
<Title> "Magao"</Title> | |
</Titles> |
________________________________|
<Titles> | |
<Title> "Head"</Title> | |
<Title> "Athena"</Title> | 2 |
</Titles> | |
__________________________|______|
Can you please elaborate your suggestion for replacing "mini XML" strings in an outer loop. Thank you!
Now the assumption is that the two pieces of mini xml will be linked to the key put in the outer XML sections (minikey1 and minikey2). Once these pieces of XML are produced (using a tXMLMap, convert them to Strings using a tConvertType. Once they are converted you can use a tJavaRow (for example) to find the String "<mini>minikey1</mini>" and replace it with ....
Once the XML String is built, use a tConvertType to convert it back to an XML Document.
You will have to also remove XML headers, but that is pretty straight forward.
It looks quite complicated, but is actually pretty straight forward so long as you keep tight control over the process.
I am extremely grateful for all your replies in this post but your solution is solving a different problem. I am reexplaining my problem in more detail. So that you can give me a suggestion upon my needs of ETL task.
My database has four tables that can be join using id. Example of tables are below:
Art Table __________________
|ID | University | |1 | University1 |
|2 | University2 |
__________________
I need outer loop on each row of Art Table.
Title Table __________________
|ID | Title | |1 | Cave |
|1 | Magoku |
|1 | Dunhang|
|1 | Magao |
|2 | Head |
|2 | Athena |
__________________
Culture and Type tables are just like Title table. All three tables are needed to be in inner loop of XML.
As I have also mentioned above my output need to be:
<System>
<Art name = "University1" id="1">
<Titles id="1">
<Title> Cave <Title>
<Title> Mogoku <Title>
<Title> Dunhuang <Title>
<Title> Mogao <Title>
</Titles>
<Types id="1">
<Type> Painting <Type>
<Type> Acrylic and ink on paper <Type>
<Type> Photography <Type>
</Types>
<Cultures id="1">
<Culture> Greek <Culture>
<Culture> Mediterranean <Culture>
</Cultures>
</Art>
<Art name="University2" id="2">
<Titles id="2">
<Title> Head <Title>
<Title> Athena <Title>
</Titles>
<Types id="2">
<Type> Drawings <Type>
<Type> Watercolors <Type>
</Types>
<Cultures id="2">
<Culture> Mogul <Culture>
<Culture> Lahore <Culture>
<Culture> Pakistan <Culture>
</Cultures>
</Art>
</System>
Therefore what I don't want to just replace mini XML between two tags only. In my case, each inner loop's table data is in as many positions as a number of rows of 'Art Table' (2 rows of Art table). For example, title table data is in subtree of both <Art name="University1"> and <Art name="University2">. Please suggest a solution for above use case. Thankyou!
