Re: Pushing XML out to a browser request -- RPG400-L

I did a little performance test:

The following concat loop ran at 1.42 sec with xmlField defined as a
fixed field and without a %trimr, with %trimr(xmlField) it actually ran
slight faster!

d xmlResult s a len(6000000) varying

for i = 1 to 10000;

xmlResult += xmlField;

endfor;

I then tried to use my powerEXT XMLNODE support that generates XML
in raw allocated storage:

for i = 1 to 10000;

xmlNode('abcdefgh':'':'abcde'); // powerEXT Node support

endfor;

without any characters to be encoded in the xml data (<> etc.) it ran at
1.01 sec and with character encoding like 'abc<>de' it ran on 1.07 sec.

So it is much faster to populate allocated storage that using large
fields and concat data!

On Thu, Oct 3, 2013 at 8:06 PM, Nicholas T Lawrence <ntl@xxxxxxxxxx> wrote:

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

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.