Re: Get XML from DB2 on the iSeries

Tom,

<snip>
This may be a very stupid question, but . . . 

Is there a non-custom-program way to get XML out of DB2 on the AS/400? 

If the answer is RTFM, a web link to the FM would be appreciated!
</snip>


There are non-custom-program ways to do it but they usually cost money.

We have already gone down this route:

Initially we considered the following options:

1) Use XML Toolkit and build our own service programs.
2) Use XML Extender and use DAD files for mapping.
3) Buy 3rd party software that can do everything for us.
4) Try and find some free software that can do (almost) everything for us.

As a programmer option 1 was my favourite.

My manager liked option 1 (he's a techie at heart) but considered option 2
or 3 may be best.

My manager's boss hated option 3 (cost) and option 4 (support) and option 2
(cost and possible impact on database performance - this is an extension to
the DATABASE).

The result of our initial meeting was: "Load the XML Toolkit onto the DEV
machine, see if it's any good to us. If it isn't then we'll consider the
other options." This cost us $250 for the DEV license, but is free if your
box is big enough.

Now, for reading and manipulating XML the toolkit is great. However, it's no
use when composing/decomposing or transforming XML. Somehow, this has been
lost during our subsequent meetings and we're still using the XML Toolkit,
but I'm having to write the apps to get the data into and out of the
database.

What I'm having to do is:

Decomposition: Write a VERY SIMPLE app in java that uses XSLT to transform
XML into SQL insert statements. These are then executed over the database.
This is unbelievably easy. Once the app is written you can put any XML into
ANY database table(s) - all of the work is done in the stylesheet. If the
XML changes, change the stylesheet and you're done.

Composition: This is trickier. The important part here is getting your
database data into ANY form of XML. Once you have some form of XML it's easy
to transform it (using XSLT again) into any other form of XML: You can merge
several XML docs, split them, transform them into HTML, flat-files, SQL
inserts, pretty much any text-based format.

To get your data into XML in the first place you can use a BRILLIANT free
tool, by Guiseppe Costagliolla, called SQL2XML (get it at
http://www.mcpressonline.com/mc?1@xxxxxxxxxxxxxxxxxxxxxx@.6aed3f76). This
allows you to use an SQL select statement to generate XML in row-col format.
You can use aliases to specify element names, you can specify a root tag,
etc... Once you have this XML you can use stylesheets to do anything with
it.

Basically, you'll be amazed how simple some of the (free) solutions can be.

I hope this has been of use to you.

Cheers

Larry