Import one XML file into multiple tables and keep the relations

One Star

Import one XML file into multiple tables and keep the relations

Hi,
I have a XML file like this :
<?xml version="1.0" encoding="UTF-8"?>
<PrintedProduct xmlns="editorialin" Name="AIXPR" NewsPaperName="BLP" PublicationDate="2009-10-08T00:00:00:000" Priority="50" Version="1">
<PrintedProductDescription Structure="B34T20">
<Section Format="Broadsheet" Layout="GABBLP" Name="A" PhysicalSectionNumber="1">
<Page FileName="B01AIX_20091008.ps" Name="B01AIX-Q" NumberInSection="1">
<Color Name="Cyan" NumberOfPlates="2" id="80001" sepId="BLP_AIXPR_0_20091008_001_C"/>
<Color Name="Magenta" NumberOfPlates="2" id="80002" sepId="BLP_AIXPR_0_20091008_001_M"/>
<Color Name="Yellow" NumberOfPlates="2" id="80003" sepId="BLP_AIXPR_0_20091008_001_Y"/>
<Color Name="Black" NumberOfPlates="2" id="80004" sepId="BLP_AIXPR_0_20091008_001_K"/>
</Page>
<Page FileName="P2BCH_20091008.ps" Name="P2BCH-Q" NumberInSection="2">
<Color Name="Cyan" NumberOfPlates="0" id="80005" sepId="BLP_AIXPR_0_20091008_002_C"/>
<Color Name="Magenta" NumberOfPlates="0" id="80006" sepId="BLP_AIXPR_0_20091008_002_M"/>
<Color Name="Yellow" NumberOfPlates="0" id="80007" sepId="BLP_AIXPR_0_20091008_002_Y"/>
<Color Name="Black" NumberOfPlates="0" id="80008" sepId="BLP_AIXPR_0_20091008_002_K"/>
</Page>
</Section>
</PrintedProductDescription>
</PrintedProduct>
I want to import this in a database like this :
Table "PrintProduct" :
------------------------
Fields :
idPrintProduct (auto increment), primary key
name
date
structure
Table "Section " :
------------------------
Fields :
idSection (auto increment), primary key
idPrintProduct (foreign key)
format
layout
name
Table "Page"
-----------------------
Fields :
idPage (auto increment), primarykey
idSection (foreign key)
fileName
name

So i set up a metadata xml to extract the data, i do a loop on the "page" element (see the capture)

So now each row extracted from the xml contains page info, section info and printedproduct info.
Now i want to feed my three tables :
- first the PrintedProduct : a new ID is generated by the mysql database,
- then the section : i need to lookup my PrintedProduct table to retrieve the newly created ID and fill the foreign key of Section table with it, a new idSection is created by the mysql database,
- then the page : i need to lookup my Section table to retrive the newly created idSection and fill the foreign key of the Page table with it, a new idPage is created.
I have absolutely no idea of how to deal with that !
Have i to create one tFileInputXML for each element (ie one for PrintedProduct, one for Section, one for Page), and deal with the feed of my tables separately ?
Any help would be very very appreciated !!
One Star

Re: Import one XML file into multiple tables and keep the relations

I have the same question.
Did you find an answer or solution ?
One Star

Re: Import one XML file into multiple tables and keep the relations

I can tell you how i've work with that :
solution one :
- read the XML file for each reference table, feed the destination table for the "reference tables" and create id's
- read the XML file then for the child rows, make a map between the previously feeded tables and feed each child tables. You have to make a join between the values of the xml and the values of the reference table (not the id of course because u dont know them in the xml), and put the id of the reference row matched in the child table.
solution two :
you can read only once the XML , then replicate the flox with tReplicate , then do the same than above with just a difference : for each flow, make a aggregate if needed.
Can be faster for huge XML files.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now