RE: xml-into

The embedded SQL users will find the XMLTABLE function in DB2 for i 7.1 an
attractive alternative, especially when database operations are involved.
http://www.ibm.com/developerworks/ibmi/library/i-using-rpg/index.html

Basically, you can query the XML document using XPath 2.0 expressions, and
then get the information you need by doing a fetch from an SQL cursor.
Whether this is the best way to go depends on your preferences and what you
are doing with the data.

A query like this one would give you one row for each pickup, and include
the shipper and consignee (The shipper and consignee are at a higher level
from pickup in the document tree, thus the XPath expressions for these will
require parent nodes (..) to be referenced).

SELECT * FROM
XMLTABLE('/loads/pickups/pickup'
PASSING XMLPARSE( DOCUMENT
' <loads>
<shipper>ACME Metal</shipper>
<consignee>Derringer Steel</consignee>
<pickups>
<pickup>Norfolk</pickup>
<pickup>Omaha</pickup>
</pickups>
</loads>')
COLUMNS
shipper VARCHAR(20) PATH '../../shipper',
consignee VARCHAR(20) PATH '../../consignee',
pickup VARCHAR(20) PATH '.'
) result_set;

I've been working with the xml-into in some testing, but I don't see
a clean way to handle an xml doc that has multiple fields, here is an
example of what I'm talking about:

<loads>
<shipper>ACME Metal</shipper>
<consignee>Derringer Steel</consignee>
<pickups>
<pickup>Norfolk</pickup>
<pickup>Omaha</pickup>
</pickups>
</loads>

When I have multiple loading points or stop offs I need to figure out
a way to handle them, without knowing up front the number of pickups
or stops....

I had created a data structure to just import the data into which
works for simple xml docs but not when I have elements that allow for
multiples...

Suggestions?

Nick Lawrence
DB2 for IBM i

Success is not final, failure is not fatal: it is the courage to continue
that counts.
- Winston Churchill