Nick
I hope to use this new functionality sometime. And thanks for your input a while back, when I was working out how to handle some XML here.
My problem with some of it was, the partner was using a DTD, not a schema, and it seems the 7.1 support doesn't work with DTDs.
XML-INTO became the easiest answer for the simple stuff I was reading, and CGIDEV2 is a dream for writing out the simple XML.
But again, I'm keeping the DB2 support in mind for something!
Vern
----- Original Message -----
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
midrange.com
