I'm not sure what is being asked here. An array host variable is generally
used to INSERT a set of rows into a DB2 table, or to FETCH a set of rows
into the array host variable. (There is also an SQL Array that can be used
in SQL procedures, but that doesn't sound applicable here)
This link talks about how to use RPG arrays with SQL:
http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajp/rzajphostarrayirpg.htm?lang=en
The idea is to retrieve or insert multiple rows using blocked IO.
It sounds like you are trying to map an RPG data structure into an SQL
value that is the XML data type.
Assuming the data structure is filled in from a FETCH, you could use an
different SQL query that returns the XML document, rather than individual
rows and columns in the array structure. The query will (probably) need
one or more XML aggregate functions to aggregate multiple rows into a
single row that has the XML document as a column. The advantage is that
DB2 figures out how to build the XML document efficiently - just like any
other SQL query. It also automatically does type conversion (for example
an SQL TIMESTAMP will be converted to a W3C dateTime).
You could also use an RPG loop to iterate over your array sub-structure
and use the SQL/XML publishing functions to explicitly build the parts of
the document that you need - and combine the parts together by passing the
intermediate XML values into the publishing functions. If you go that way,
then the advantage of SQL/XML is diminished, since you are not actually
working with DB2 for anything other than XML construction.
This white-paper has already been talked about on this thread and has some
examples
https://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler?contentId=K$63TzTFkZwiPCA$cnt&roadMapId=IbOtoNReUYN4MDADrdm&roadMapName=Education+resources+for+IBM+i+systems&locale=en_US
You can also find some tutorials on SQL/XML in the IBM Knowledge Center:
http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/sqlp/rbafyxml3909.htm?lang=en
I agree SQL/XML is an advanced SQL topic, since it requires both knowledge
of the relational (SQL) & XML data models...and in this case RPG knowledge
is needed as well. The DB2 for i Center of Excellence is a good place to
acquire additional education or consulting in these areas. You can find
their link at:
http://www-03.ibm.com/systems/services/labservices/platforms/labservices_power.html
Nick Lawrence
DB2 for IBM i
I am stretching my SQL capabilities in an attempt to write XML that
reflects a data structure, but it's clear I don't really know what I'm
doing.
Working in i OS 7.1 with TR7, most PTFs up-to-date (but no promises
there).
All the examples (articles, archives, Knowledge Center, etc.) I've come
across just deal with mapping from files, so I'm having difficulty with
the data structure aspect. I am trying to create the XML for a data
structure that has some nested arrays, but otherwise not too involved.
Something on the order of:
d AccountData ds qualified
d Notify n
d EmailTo 50a
d BillPhones likeds(PhoneDetail) dim(32)
d nbr_Phones 3u 0 inz
d BillPDFs likeds(PDFnames)
d PhoneDetail ds qualified
d Phone 10 0 inz
d PriorPayments likeds(payments) dim(16)
d nbr_Payments 3u 0 inz
d Payments ds qualified
d Amount 8 2 inz
d PaidDate 10a
d PDFnames ds
d PDFcount 3u 0 inz
d PDFid 23a dim(25)
Tools that create XML (with basic examples) are interesting, but I've
yet to see any examples of how to write XML that has nested arrays I need.
I may reconsider CGIDEV2 or XMLi, over my current efforts in SQL/XML if
someone can help me past this hurdle.
My end result is a payload for a web service response to a business
partner.
midrange.com
