Re: Help needed with SQL select from XMLTABLE

date: Mon, 24 Feb 2025 20:57:05 +0100
from: Daniel Gross <daniel@xxxxxxxx>
subject: Re: Help needed with SQL select from XMLTABLE

Hi Gad,

when I try to run your query with SYSTOOLS.HTTPGETBLOB I'm receiving an
more or less helpful error message.

After poking around on 7.5 with QSYS2.HTTP_GET and QSYS2.HTTP_GET_BLOB I
found out, that the SSL connection could not be established, due to an
server certificate that was not signed by an authorized certificate
instance.

First - if possible (7.4 or 7.5) you should replace the
SYSTOOLS.HTTPGETBLOB call with a call to QSYS2.HTTP_GET_BLOB - the new
QSYS2 functions are so much faster.

Second - to get around the certificate error, you have different options:

a) download the certificate of the server and store it in the system
certificate store

b) download the certificate of the server into a certificate file and use
this

qsys2.http_get_blob(
'
https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml',

'{"sslCertificateStoreFile":"/home/TrustStore/CertStore.KDB"}'
)

c) ignore the certificate signing error by setting option "sslTolerate" to
true:

qsys2.http_get_blob(
'
https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml',
'{"sslTolerate":"true"}'
)

Read more about the new HTTP_GET functions here:
https://www.ibm.com/docs/en/i/7.5?topic=functions-http-get-http-get-blob

HTH and kind regards,
Daniel

Am 24.02.2025 um 19:43 schrieb Gad Miron <gadmiron@xxxxxxxxx>:

?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