topic Re: Generate n rows of XML output in Talend Studio

Generate n rows of XML output

Anonymous — Thu, 31 May 2018 06:59:59 GMT

Hi,

I have read plenty of discussions on this forum that ask as to how n number of rows can be split into batches.

I am facing a very similar situation and the link provided by @rhall is no longer available.

So, I am re-posting the question here again:

I have a query in tOracleInput that generates n number of rows.

The rows read are then joined with some tHashOutput components to generate XML data.

This XML data is actually a SOAP request that performs bulk operations.

However I recently found out that I have to limit the number of rows I send in a SOAP request to 1000.

Any idea how I can achieve that?

My job currently looks something like below

tOracleInput ----> tXMLMap ----> tSOAP ----> tExtractXMLFields ----> tMap ----> tMSSqlOutput

^ ^ ^

| | |

| | tHashOutput3

| |

| tHashOutput2

tHashOutput1

Re: Generate n rows of XML output

TRF — Thu, 31 May 2018 10:16:58 GMT

Sometimes best is the enemy of good.

So, you may probably use a tMap instead of a tXMLMap to produce CSV files with a number of records limited to 1000 (tFileOutputDelimited Advanced Settings).

Then, on the next subjob, iterate over the list of CSV files generated by the 1rst subjob to build the expected XML and call the SOAP request for each input file.

Re: Generate n rows of XML output

Anonymous — Thu, 31 May 2018 11:34:37 GMT

Thanks @TRF I am thinking of something similar... tMap ----> tAdvancedFileOutputXML and then 2nd subjob that iterated over the XML files generated and pass them on to tSOAP.

Re: Generate n rows of XML output

TRF — Thu, 31 May 2018 11:45:00 GMT

That's also an option but the solution with intermediate CSV files will be faster.

Re: Generate n rows of XML output

Anonymous — Thu, 31 May 2018 12:21:28 GMT

The solution I posted to this (sorry, my server was attacked by Russian hackers.....according to their IP addresses and I haven't had the time to sort it yet), is as follows.

1) You know that you are limited to 1000 rows of data. So write a mechanism into your initial query that lets you group the data in a maximum of 1000 records. Find a key (or add a key) to enable this. This can be composite.

2) Once you have figured out the grouping mechanism in your query, add a new t{database}Input component at the beginning of your job and use that query to return just the group keys. So if you you data has 10000 rows broken into 15 groups with 3 key fields, your query will return 15 rows with your 3 key fields.

3) Connect this db component to a tFlowToIterate. This will put your key fields into the globalMap and allow you to iterate. THIS is the key step.

4) Now connect your original input db component to the iterate link. Use the globalMap values in your query's where clause. This will mean your query will only return a max of 1000 rows. The rest of the job should remain pretty much the same.

What you are doing here is essentially looping over your dataset in iterations per group or per 1000 rows in a group. What comes after the iterate link is essentially treated as its own subjob, so it allows you to limit the number of records to supplied to the xml and produces an xml document per iteration.

Re: Generate n rows of XML output

Anonymous — Thu, 31 May 2018 15:02:28 GMT

So I am planning to go with your approach as it seems simpler to me
Just one question, do you by any chance know how we can not output an XML tag that is empty?

Re: Generate n rows of XML output

Anonymous — Thu, 31 May 2018 15:09:30 GMT

In the tXMLMap component, click on the spanner in the output table and set "Create empty element" to false.

Re: Generate n rows of XML output

Anonymous — Fri, 01 Jun 2018 05:42:51 GMT

I am using a tMap instead of tXMLMap so this option is not availalble in tMap. But yes a similar option exists in the tAdvancedOutputXML component which I am using and have enabled.