XML files Creation dynamically

srikan3 · Post by **srikan3** » Tue May 17, 2016 8:11 am

Hi All,

Here is my requirement, it will be great help if someone can help me in my current XML requirement.

We need generate XML files from source DB2 database. We have more than 2000 different tables. We just need to extract data from DB2 tables and generate each XML for each table.

I have XSD file for each table.

Can we do it in single job ?

eostic · Post by **eostic** » Tue May 17, 2016 10:56 am

Possibly.... consider using the xml Transformer.....and then, based on the table you are extracting, dynamically build the Stylesheet for xslt that will craft the xml you desire for that particular table....

Not easy...you have to work out the stylesheet yourself and figure out how to generate it...but you can pass the name of that stylesheet dynamically, and also, often when converting rdbms to xml, there is a fairly simple and consistent format.

The actual stages for building formal xml (xmlOutput and Hierarchical Stage [or xml stage depending on your release]) require fixed and compiled metadata for the xml schema you are writing for.

Ernie

srikan3 · Post by **srikan3** » Wed May 18, 2016 5:10 am

Thanks Ostic for the reply, but for XML transformer stage we need to give "XML source column", how we will give this source column name.

Can anyone else resolve my problem ?

ray.wurlod · Post by **ray.wurlod** » Wed May 18, 2016 3:54 pm

There are many options to generate the XML source column, including populating the column from DB2 directly, or using a Column Export stage, or using a Transformer stage.

srikan3 · Post by **srikan3** » Thu May 19, 2016 3:13 am

Thanks Ray for your response,

We are using RCP as we need to pass the columns names dynamically, is there anyway to pass "XML source column" dynamically ?

eostic · Post by **eostic** » Thu May 19, 2016 6:14 am

This is a guess...but is there any way, via Modify or Column Export (or some other Stage) to force all the columns [with RCP] into one single column with a delimeter? ....such that you could declare a single column on the output link of a (say) a Column Export or Modify, and then have that be the single declared column going into the xml Transformer? ...and then have a single declared column out of the xmlTransformer? It could/would still be entirely generic, because it would be driven by your columns coming in that get dynamically concatenated. I haven't exercised those two stages or others with RCP to determine if you can do a dynamic "many cols" to "one declared col" easily.

That still may not solve things, as you would have to experiment heavily with the XML Transformer and xslt to see if you could convert a single comma delimited string into the xml you desire, on a row by row basis...but maybe..........?

Ernie

ray.wurlod · Post by **ray.wurlod** » Thu May 19, 2016 4:40 pm

Why can't you use a Column Export stage? It's inputs are defined by RCP, you can name a single column for its output without violating your overall requirement for RCP as this column will function dynamically.

srikan3 · Post by **srikan3** » Fri May 20, 2016 3:03 am

I am using XML transformer stage to generate XML file from the source DB2 database, here for single file one job is working fine, but not able to convert the same job for multiple jobs.

eostic · Post by **eostic** » Fri May 20, 2016 4:06 am

Rays point is for you to explicitly create a column, call it "myXMLinput" out of a Column Export (ie...zero columns in, using RCP, one fixed column out). That single column will have all the data. Then pass that into your xmlTransformer, with one column out (myXMLoutput).

It will require a lot of experimentation and probably some creative xslt, to convert delimited data into xml.

Ernie

DSXchange

XML files Creation dynamically

XML files Creation dynamically

For XML transformer stage do we need XML as INPUT ?

But we are not defining metadata in my case

Its working for fine single table extraction