RE: RPG400-L Digest, Vol 13, Issue 470 -- RPG400-L

Nick,

It sounds like you are trying to map an RPG data structure into an SQL
value that is the XML data type.

You got the gist of my mission well-stated. It matters not to me if the result is an XML data type or a varchar. (I don't know if the receiving system can handle the XML data type or not). It just needs to look like XML in a SOAP web service response.

This link talks about how to use RPG arrays with SQL:
http://www-
01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajp/rzajphostarrayirp
g.htm?lang=en

This link got me a "504 Gateway Error". I'll see if I can find my way into that another way -- looks intriguing. Thanks.

... The DB2 for i Center of Excellence is
a good place to acquire additional education or consulting in these
areas.

I agree that I am in a bit over my head here, and need more training in this, or at least to get an assist with this project. I may end up exploring this. Thanks for that as well.

-- Michael
~~~~~~~~~~~~~~~~~

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Nicholas T Lawrence
Sent: Tuesday, November 18, 2014 11:32 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: RPG400-L Digest, Vol 13, Issue 470

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/rzajphostarrayirp
g.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$63TzTFk
ZwiPCA$cnt&roadMapId=IbOtoNReUYN4MDADrdm&roadMapName=Education+resource
s+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?l
ang=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.htm
l

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.