topic Re: Parse an XML and write datas on database. in Talend Studio

Parse an XML and write datas on database.

Anonymous — Thu, 18 Apr 2013 14:01:48 GMT

Hi,
I'm new to Talend and I need a suggestion because I'm quite sure the way I solved my problem is quite far from being the best one.
First of all sorry for my bad English!
That's the scenario:
I need to parse an xml file, extract various elements and than perfom several SQL "insert" whose value depends on each retrieved XML's element value.
The way I do it is shown in the first image.
1) I parse the xml file (e.g.100 elements) and then I create 100 new records on a db's table using the tMySQLOutput module (the one called first in the first image).
In this case Talend performs a "bulk" write from XML to db.
2) I examine again the 100 elements parsed from the XML file and than I need to write other values that the 100 elements contain on various db's tables.
Before writing each of the 100 elements I need to perform various tests (test1, test2 and test3 in the first image) to correctly set up the FK in every record I'm going to create.
The only way I found to accomplish point 2 is to use tMySQLRow to write the set of records corresponding to the first XML element, before examining the second XML element and so on.
This solution works but is really hard to debug the job when something goes wrong during the execution.
I'd like to use a tXMLMap and a tMySQLOutput like I do at the point one, it wouyld be much more easy to debug my job.
The problem, as shown in the second image, is that with tXMLMap and tMySQLOutput I can only perform a bulk write and I can't do the tests I need to correctly set up FKs.
In addition to this I can't connect a tMySQLoutput as a "writeORnot-trigger" to a tXMLMap.
Can someone show me the way to fix the job shown in the second image?
Thanks a lot!!!

Re: Parse an XML and write datas on database.

alevy — Fri, 19 Apr 2013 05:02:58 GMT

Without really understanding what you're trying to do: can you not tMap with lookups and filtered outputs instead of your tFlowToIterate etc.? Also, if you're iterating to create individual rows, you can combine the iterations back into one flow with tIterateToFlow or tUnite.

Re: Parse an XML and write datas on database.

Anonymous — Fri, 19 Apr 2013 08:32:30 GMT

can you not tMap with lookups and filtered outputs instead of your tFlowToIterate etc.?

mmmmm alevy I think I've not understood what do you mean with "with lookups" and "filtered output".
Which t-modules do you refer?
Matteo

Re: Parse an XML and write datas on database.

alevy — Mon, 22 Apr 2013 00:55:50 GMT

I'm referring just to tMap. All I can see from your job is that you're reading from the DB and doing stuff in tJavaRow before writing with tMySQLRow. I think you should be able to bring all your requirements for the processing logic together in tMap, replacing tFlowToIterate, tJavaRow and the If connections. You'll need to provide more detail on exactly what you're trying to do if you want more help.

Re: Parse an XML and write datas on database.

Anonymous — Mon, 22 Apr 2013 08:18:22 GMT

I'm referring just to tMap. All I can see from your job is that you're reading from the DB and doing stuff in tJavaRow before writing with tMySQLRow. I think you should be able to bring all your requirements for the processing logic together in tMap, replacing tFlowToIterate, tJavaRow and the If connections. You'll need to provide more detail on exactly what you're trying to do if you want more help.

Ok now I got what you mean
Sorry but I've got such little experience with Talend.
I'd really love to use only a tMap to perform all the verifications I need but how can I implement something like:
read the value N from Values.xml file, compare it with the result of the query "SELECT table.id FROM db.table WHERE table.name=XYZ", if N=id perfom "INSERT INTO ....." otherwise skip to the next row of the Values.xml file
only using a tMap module?
Onche again thanks for your help!
Matteo

Re: Parse an XML and write datas on database.

alevy — Mon, 22 Apr 2013 08:41:50 GMT

That sounds like using a very simple lookup with inner-join in tMap, passing the output to tMySQLOutput. I suggest you start by closely reading the tMap component help to understand how it works and maybe look at some of the tutorials on the website. Then, if you have a specific problem achieving what you want, start a new topic with the details.

Re: Parse an XML and write datas on database.

Anonymous — Tue, 23 Apr 2013 07:54:30 GMT

Hi Matteo,
I think these references will be helpful for your working:
tMap, tMap operation and tMap Job example.
Best regards
Sabrina

Re: Parse an XML and write datas on database.

Anonymous — Tue, 23 Apr 2013 08:55:17 GMT

Thanks xdshi and alevy, I should have studied more before start working with Talend but time to market is really killing me!