× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.