If you want to decompose an XML Document with XMLTABLE, there is no need to
move anything into a variable, a table. You can consume the webservice
directly.
Example:
Select *
From XMLTABLE('geonames/code'
Passing XMLParse(Document(
httpGetClob('
http://api.geonames.org/postalCodeSearch?postalcode=86916&maxRo
ws=10&username=xxxxxx',
'')))
Columns "postalcode" VarChar(10),
"name" VarChar(50),
"countryCode" VarChar(3),
"lat" Dec(11, 5),
"lng" Dec(11, 5)) x;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Tim
Fathers
Sent: Donnerstag, 16. September 2021 11:46
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Good way to identify malformed XML?
I think using locators is even easier, isn't it?
DCL-S YourCLOB SQLTYPE(CLOB_LOCATOR);
DCL-S YourXML SQLTYPE(XML_LOCATOR);
Exec SQL Set YourCLOB = 'ABC';
Exec SQL Set YourXML = xmlparse(document
httpgetCLOB('
https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml',
'' ));
..and from here the XML can be parsed directly using XMLTABLE and passing
the locator....
Tim.
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of
Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
Sent: 16 September 2021 04:28
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Good way to identify malformed XML?
You can define a CLOB variable with the Keyword SQLTYPE:
DCL-S YourCLOBVar SQLTYPE(CLOB: 16000000);
... but a CLOB Variable is restricted to 16MB (RPG's limit of character
variables).
That's why I suggested to use an SQL Global Variable, which is a permanent
Object that works similar to a data area in the QTEMP, i.e. the same SQL
Global variable can have different values in different jobs.
An SQL LOB (CLOB, BLOB, DBCLOB, XML) can be defined up to 2 GB.
An SQL Global Variable can only be accessed in a SQL Statement, i.e. you
either need a SET or VALUES ... INTO statement to populate your SQL Global
Variable.
Examples for Generating SQL Global Variables Create Variable
YourSchema/YourglobalCLOB Clob(2G) Default ''; Create Variable
YourSchema/YourglobalXML Xml Default Null;
Examples for populating SQL Global Variables:
Exec SQL Set YourSchema.YourGlobalCLOB = 'ABC'; Exec SQL Set
YourSchema.YourGlobalXML = xmlparse(document
httpgetCLOB('
https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml',
'' ));
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin
Taylor
Sent: Mittwoch, 15. September 2021 20:59
To: MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Good way to identify malformed XML?
How do you define those in RPG? When I try to use a large char(), the SQL
precompiler gives me:
SQL0312: Position 22 Variable RDBUF not defined or not usable. Reason: No
declaration for the variable exists, the declaration is not within the
current scope, or the variable does not have an equivalent SQL data type.
Thanks
date: Wed, 15 Sep 2021 05:17:45 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: Good way to identify malformed XML?
You can move big amounts of data (up to 2 GB) it into SQL Global
Variables.
When trying to move the data into a SQL Global Variable with Data Type
XML, you can directly use the XMLPARSE.
Alternatively you can try to insert the XML document into a
(temporary) table with an XML column. When inserting it is not even
necessary to execute the XMLPARSE. The insert will fail for an
non-wellformed XML document.
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"
?Train people well enough so they can leave, treat them well enough so
they don't want to.? (Richard Branson)
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
As an Amazon Associate we earn from qualifying purchases.