One Star

[resolved] Complex XML structures to MySQL

Hi guys,
I have some XML files that come in a format like this:
<entry>
  <lastName>ORIGINAL NAME</lastName>
  <akaList>
     <aka>
        <lastName>AKA NAME</lastName>
     </aka>
  </akaList>
  <addressList>
     <address>
        <street>STREET 1</street>
        <country>NEVERLAND 1</country>
     </address>
     <address>
         <street>STREET 2</street>
         <country>NEVERLAND 2</country>
     </address>
  </addressList>

I need to put these XML files into a database table with name, address, country columns. The problem: I need a separate row with name, address, country for EVERY name (original name and aka. name). For this example it would look like:
name;address;country
ORIGINAL NAME; STREET 1;NEVERLAND 1;
ORIGINAL NAME; STREET 2;NEVERLAND 2;
AKA NAME;STREET 1;NEVERLAND 1;
AKA NAME;STREET 2;NEVERLAND 2;
I've tried solving this using tFileInputXML, but I don't know if that is even possible with a single component. Do you guys have any idea how to do that?
Thanks in advance and greetings from Vienna
Hannes
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: [resolved] Complex XML structures to MySQL

Hi 
Read the xml file two times with two tFileInputXML component, one tFileInputXML reads the original name, street and country, another one reads the aka name, street and country, and then merge the two input flow with tUnite.

 

Result printed on console:
 Starting job forum37818 at 19:56 21/08/2014.
connecting to socket on port 3950
connected
.-------------+--------+-----------.
|            tLogRow_1             |
|=------------+--------+----------=|
|name         |street  |country    |
|=------------+--------+----------=|
|ORIGINAL NAME|STREET 1|NEVERLAND 1|
|ORIGINAL NAME|STREET 2|NEVERLAND 2|
|AKA NAME     |STREET 1|NEVERLAND 1|
|AKA NAME     |STREET 2|NEVERLAND 2|
'-------------+--------+-----------'
disconnected
Job forum37818 ended at 19:56 21/08/2014.


Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
1 REPLY
Community Manager

Re: [resolved] Complex XML structures to MySQL

Hi 
Read the xml file two times with two tFileInputXML component, one tFileInputXML reads the original name, street and country, another one reads the aka name, street and country, and then merge the two input flow with tUnite.

 

Result printed on console:
 Starting job forum37818 at 19:56 21/08/2014.
connecting to socket on port 3950
connected
.-------------+--------+-----------.
|            tLogRow_1             |
|=------------+--------+----------=|
|name         |street  |country    |
|=------------+--------+----------=|
|ORIGINAL NAME|STREET 1|NEVERLAND 1|
|ORIGINAL NAME|STREET 2|NEVERLAND 2|
|AKA NAME     |STREET 1|NEVERLAND 1|
|AKA NAME     |STREET 2|NEVERLAND 2|
'-------------+--------+-----------'
disconnected
Job forum37818 ended at 19:56 21/08/2014.


Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business