My XML seems like that :

File 1:
------------
<?xml version="1.0" encoding="UTF-8"?>
<book type="novel" dateOfIssue="11/10/2009" author="king">
  <page number="1" color="Q" file="1092.ps"/>
  <page number="2" color="Q" file="1093.ps"/>
  <page number="3" color="B" file="1094.ps"/>
</book>

File 2:
------------
<?xml version="1.0" encoding="UTF-8"?>
<book type="novel" dateOfIssue="7/12/2009" author="clarke">
  <page number="1" color="B" file="2092.ps"/>
  <page number="2" color="B" file="2093.ps"/>
  <page number="3" color="B" file="2094.ps"/>
</book>


And the associated table (output)

BookType :
ID - Value
1      novel

Book :
ID Booktype_ID dateOfIssue Author
1          1          11/10/2009    King
2          1          7/12/2009     Clarke

Page
ID Book_ID Color     File
1       1          Q     1092.ps
2       1          Q     1093.ps
3       1          B     1094.ps
4       2          B     2092.ps
4       2          B     2093.ps
4       2          B     2094.ps

Hello,

I'm new in ETL tools and Talend too, so forgive me if my question looks stupid

As input, I have to deal with XML files containing books and pages of these books.
The book as a type, which is an attribute of the xml element "book" (for instance, type="novel")

I want to have as output a mysql database with the XML data loaded in, in three tables :
-> a ref table "Book type"
-> a table "book" with a link on "book type"
-> a table "page" with a link on a "book"

I want the load to be "on the fly" , for instance:
1/ the first xml file contains a book of type" novel : reading of the "book type" ref table, look up of the ref, "novel" doesn't exists inside the ref table so creation of a row in "book type", creation of the book (1 row in "book" table)  and link with the new book type row created, the creation of the pages.

2/ the second xml file contains a book type "novel" tool : reading of the "book type" ref table, lookup, "novel" exists so creation of the book and link with the existing row in the "book type" table.

How to proceed with talend ? What component must i use?

I can read my xml file in a "non normalized" way and display it (ie a line displayed = a page with informations on the page + infos on the book + infos on the book type) but i don't know how to proceed now !

Thanks in advance,




Merci d'avance à tous.