|
I absolutely agree with Jon. Have you ever tried to import an XML invoice or purchase order in an UBL format, even worse if it is an EN16931 format.
Forget SQL immediately. Unusable. In addition, the effort is much greater. First define the data structures and then a SQL statement for each field.
With XML-Into you define an embedded XML template and read the XML with a single statement. It's also nice that you can also define the number
of elements that are read in. Any questions?
Oh yes, I have exactly this experience with many formats of electronic invoices and orders that I process in this way.
For simpler imports, the SQL way may be an alternative.
Just my 2cts.
kf
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] Im Auftrag von Jon Paris
Gesendet: Montag, 20. Januar 2025 18:20
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: XML-INTO %HANDLER PATH
No it wasn't Vern - I was responding generically.
If only a limited number of elements are required, then the SQL approach is often a good one.
The SQL syntax for XML is just so .... nasty IMHO. In the case of JSON they did a much better job.
As to omitting unwanted elements with XML-INTO you simply omit them from the DS and specify allowextra=yes in the options. use the path to drill down to the start point.
Jon Paris
Jon.Paris@xxxxxxxxxxxxxx
On Jan 20, 2025, at 12:09 PM, Vern Hamberg via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx> wrote:
Hi Jon
I hope my reply wasn't an automatic "SQL is the answer". I have found that XMLTABLE is easier to use than the data-structure approach with XML-INTO. I admit that I did not pursue further the use of XML-INTO, once XMLTABLE gave me everything I wanted in a fairly simple way.
I do see that the size limit might exist for SQL, especially if it also uses the SAX method - I don't know, one of the Db2 team would need to speak to that.
Defining data structures to be able to flatten XML structures into rows just escapes me, the Xpath stuff lets me be selective - maybe you can verify, do we have to declare data structures to contain every layer of the XML structure, or can we specify only the elements we need, some from a parent level, others from child levels perhaps even child of child, etc.
Cheers
Vern
On 1/20/2025 10:38 AM, Jon Paris wrote:
Not a question of "favorite solution!" Vern - but I do get really tired of "The answer is SQL - now what was the question?"--
I also suspect that SQL may also have issues with the size of the document if RPG is barfing at a 2G limit. SAX similarly may also have an issue as I seem to recall that the entire doc is checked for validity before the parser does its thing.
Maybe noXDB can handle it.
Jon P.
I find that XMLTABLE can look a lot like CREATE TABLE, with columns defined with regular SQL data types, plus "path" specs for where each element is located in the XML.
See if that article helps - if you need more, I can probably help, I give a case study presentation about this.
Cheers
Vern
On 1/19/2025 7:12 PM, Charles Wilt wrote:
Actually, that "process in chunks" idea is not quite right...--
Even with %handler and path being used, XML-INTO pukes at 1GB; with the
default UCS processing.
You can double that limit to 2GB by telling it to process the file as SBCS.
https://www.ibm.com/docs/en/i/7.5?topic=documents-limitations-xml-parser
There's been an idea out there for a while for IBM to increase the limit.
https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-1826
Charles
On Sun, Jan 19, 2025 at 3:50 PM Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:
Are you sure Dave?
If you are using %handler you are processing the xml in chunks of the size
you define - surely the size of parts within any given object is not going
to exceed 16mb is it?
Jon Paris
Jon.Paris@xxxxxxxxxxxxxx
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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.
midrange.com
