Building the XML document with an SQL query means that the database will
efficiently retrieve the data and build the XML document for you.
The XML value could be stored in a host variable (as a BLOB or CLOB),
written out to an IFS file, returned as a column of a result set to the
application, etc.
Here's an example of an RPG program that runs the query in the mentioned in
the blog entry (
http://db2fori.blogspot.com/2013/02/integrating-xml-past-present-and-future.html
), and dumps the resulting XML document to the standard output stream. In
this example, the XML is serialized to a CCSID 37 CLOB, although a BLOB
data type would generally work better. (The encoding is included in the
resulting document, and using a BLOB prevents accidental CCSID conversion)
The blog has a diagram of the tables being joined, and more explanation of
the specifics of this query.
You should also look at this white paper
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
The paper is mentioned in the blog and includes many SQL/XML & RPG
examples.
H MAIN(EXAMPLE)
****************************************************
* Procedure Prototype
****************************************************
DEXAMPLE PR EXTPGM('EXAMPLE')
****************************************************
*Function to print to standard Out
****************************************************
D QtmhWrStout PR extproc('QtmhWrStout')
D DtaVar 32767A options(*varsize) const
D DtaVarLen 10I 0 const
D ErrorCode 8000A options(*varsize)
D ErrCode DS qualified
D BytesProv 10I 0 INZ(0)
D BytesAvail 10I 0
*****************
* Host variables
*****************
D ReqOrderId S 5I 0
D Clob_Result S SQLTYPE(XML_CLOB:3000)
* It's more normal to use CCSID 1208 for XML/web data
* But 37 is easier to read in an interactive environment
* A BLOB is in general a better choice for XML
C/EXEC SQL
C+ DECLARE :Clob_Result VARIABLE CCSID 37
C/END-EXEC
************************************************
*Procedure implementation
************************************************
PEXAMPLE B
D PI
/FREE
//For this example, hard code ReqOrderId to 2
ReqOrderId = 2;
// Run Query, store XML document in a host var Clob_Result
EXEC SQL
WITH order_part_list AS (
SELECT order_id,
XMLGROUP(part_code AS "code",
quantity AS "quantity"
ORDER BY quantity DESC
OPTION ROW "part"
ROOT "part_list"
)
AS part_list
FROM order_part
GROUP BY order_id
)
SELECT XMLDOCUMENT(
XMLELEMENT(NAME "order",
XMLFOREST(order_id AS "id",
order_time AS "time",
XMLFOREST(customer_name AS "name",
customer_address AS "address")
AS "customer",
opl.part_list AS "part_list"
)
)
) AS order_doc
INTO :Clob_Result
FROM order
INNER JOIN customer
USING (cust_id)
INNER JOIN order_part_list opl
USING (order_id)
WHERE order_id = :reqOrderId;
// dump results to standard output
QtmhWrStout(Clob_Result_Data :Clob_Result_Len :ErrCode);
/END-FREE
P E
Nick Lawrence
DB2 for IBM i
Success is not final, failure is not fatal: it is the courage to continue
that counts.
- Winston Churchill
message: 4
date: Thu, 3 Oct 2013 00:13:22 +0200
from: Henrik R?tzou <hr@xxxxxxxxxxxx>
subject: Re: Pushing XML out to a browser request
Nick
please place a SQLRPGLE example of your example
On Wed, Oct 2, 2013 at 11:46 PM, Nicholas T Lawrence <ntl@xxxxxxxxxx>
wrote:
I think you should consider the SQL/ XML Publishing functions in DB2 for
i
7.1
What you have is a problem very similar to the one described on Mike
Cain's
blog at
http://db2fori.blogspot.com/2013/02/integrating-xml-past-present-and-future.html
(Except of course you are creating the document yourself without an IBM
or
3rd party product)
Note: The SQL/XML publishing functions will not build a "CDATA" section
since this is not a part of the XML data model. However, they will
properly encode the values retrieved from the database, making this a
non-issue for the receiving application. The result is your XML document
will (probably) be smaller on average, since many of your values won't
need
special encoding (I assume).
Nick Lawrence
DB2 for IBM i
midrange.com
