Seven Stars

Generate n rows of XML output

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_2_0 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

1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: Generate n rows of XML output

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.

7 REPLIES
Forteen Stars TRF
Forteen Stars

Re: Generate n rows of XML output

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.


TRF
Seven Stars

Re: Generate n rows of XML output

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.

Forteen Stars TRF
Forteen Stars

Re: Generate n rows of XML output

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


TRF
Fifteen Stars

Re: Generate n rows of XML output

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.

Rilhia Solutions
Seven Stars

Re: Generate n rows of XML output

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

Re: Generate n rows of XML output

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

Rilhia Solutions
Seven Stars

Re: Generate n rows of XML output

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.