topic Re: Converting one-to-many database row into XML data in Talend Studio

Converting one-to-many database row into XML data

Anonymous — Tue, 03 Sep 2013 21:59:32 GMT

I have a database with the following tables:
OBJECT_MAIN
object_id | title
98 | beads
99 | plates
100 | bronze
OBJECT_REGION
object_id | region
98 | egypt
98 | alexandria
99 | greek
I would like to convert each object data into a XML file with
<culture_object object_id="98">
<title>beads</title>
<region>
<location>egypt</location>
<location>alexandria</location>
</region>
</culture_object>
Here is what I did:
I used two tMySQL input source for the two table and linked to tMap with Main and Lookup (join by object_id).
But, it shows only first region, not the second, and it does not show item 100.
How can I map multiple regions of the row into the XML file?

Re: Converting one-to-many database row into XML data

Anonymous — Wed, 04 Sep 2013 20:11:51 GMT

Hi Venturi,
You need to select "All matches" in the component called tMap for the Match Model option (see screenshot : tMap.png).
As you can see your Job should look like the one attached in the screenshot Job.png (disregard the components used for the input since I don't have your MySQL table; so I fake it with your sample data and the tFixedFlow components).
You need also to pay attention to the configuration of the component tAdvancedXMLOutput to have the same expected result as mentioned above in your Post (see screenshot : tAdvancedXML.png).
Let me know if that helps.
Thanks.

Re: Converting one-to-many database row into XML data

Anonymous — Sat, 07 Sep 2013 22:26:22 GMT

cantoine,
Thanks you very much for the help.
I tried exactly as you did with tMap setting and the output XML setting.
However, I got four records instead of three.
The record 98 is created with two XML records: one with the first location, the other with the second location. I was hoping that it will put both "region" fields in the same "region" tag as two child "location" tag in one XML record instead of splitting them into two separate files.
That is instead of

<root>
  <culture_object object_id="98">
    <title>beads</title>
    <region>
      <location>egypt</location>
    </region>
  </culture_object>
</root>
<root>
  <culture_object object_id="98">
    <title>beads</title>
    <region>
      <location>alexandria</location>
    </region>
  </culture_object>
</root>

It should be

<root>
  <culture_object object_id="98">
    <title>beads</title>
    <region>
      <location>egypt</location>
      <location>alexandria</location>
    </region>
  </culture_object>
</root>

Re: Converting one-to-many database row into XML data

Anonymous — Sat, 07 Sep 2013 22:41:20 GMT

I tried it again and found that the issue I mentioned above was caused by my splitting up each XML record into separate file. Once I combine them into one XML in tAdvancedFileOuptutXML setting, it works perfectly.
Thanks a lot!

Re: Converting one-to-many database row into XML data

Anonymous — Fri, 13 Sep 2013 01:01:40 GMT

cantoine,
Is there anyway to combine the child tags in a single XML file as I described above to output each record as separate XML file. Right now it produces multiple XML files for based child tag.

Re: Converting one-to-many database row into XML data

Anonymous — Fri, 13 Sep 2013 03:15:26 GMT

Hi venturi
Add a tFlowToIterate between tMap and tAdvancedXMLOutput to iterate each record and set a dynamic output file path on tAdvancedXMLOutput, for example.
...tMap---out1-->tFlowToIterate--iterate-->tFixedFlowInput--main--tAdvancedXMLOutput
on tFixedFlowInput: generate the current data flow, for example: define the following columns on the schema:
column: value
object_id (Integer/int type): (Integer)globalMap.get("out1.object_id")
title (string type): (String)globalMap.get("out1.title")
region( string type): (String)globalMap.get("out1.region")
On tAdvancedOutputXML, set a dynamic file path so as to generate a separater XML file for each record, for example:
"D:/file/"+(String)globalMap.get("out1.region")+".xml"
Shong

Re: Converting one-to-many database row into XML data

Anonymous — Wed, 28 Oct 2015 04:32:44 GMT

I have another doubt.. How to apply "ALL in One" property in tWriteJSONField.. I know about tXMLMap but I want to perform this property with tWriteJSONField.. so please explain with example. I am beginner in Talend