RE: Help needed with SQL select from XMLTABLE

They changed their policy:
Change the HTTP_GET statement as follows:
HTTP_GET('https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml',
'{"sslTolerate":"true"}')

... and it should works (at least it works for me)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"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)
"Learning is experience ? everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Gad
Miron
Sent: Monday, 24 February 2025 19:43
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Help needed with SQL select from XMLTABLE

hello sages

For the last 9-10 years I have been pulling Euro exchange rates from the
European Central Bank using the following partly comprehensible SQL..
This SQl statements started failing a week or so ago.
I admit to an (almost complete) ignorance of the XMLTABLE SQL function and
would greatly appreciate a pointer or two (or three) TIA Gad

insert into GAD/EXCHGRATEF select RATE_TIME as DATE,'EUR' as
BASECURR, CURRENCY as CURRCDE, RATE as EURRATE, RATE as
CURRRATE, 'European Central Bank' as SOURCE

from (SELECT my_cube.rate_time, my_cube.currency, my_cube.rate

FROM XMLTABLE(XMLNAMESPACES( DEFAULT '
http://www.ecb.int/vocabulary/2002-08-01/ eurofxref' , '
http://www.gesmes.org/xml/2002-08-01' AS "gesmes" ) ,

'gesmes:Envelope/Cube/Cube/Cube' PASSING XMLPARSE(DOCUMENT
SYSTOOLS.HTTPGETBLOB( '
https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml' , '' )
)

COLUMNS currency CHAR(3) PATH '@currency' , rate DECIMAL(10,4) PATH
'@rate' , rate_time DATE PATH '../@time' ) my_cube ) tmp
--
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.