|
Hello everyone! I got a task to daily read exchange rate from site: https://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.xml I would like to do this with Stored procedure in SQLA, read this XML and write data in table. Could someone guide me how to return this XML in stored procedure? I am using SQLA 17. Regards Tomaz |
|
I don't have a sample available but some hints/keywords:
Here's a FAQ with a similar web call for ECB exchange rates. Hi Barth, thanks for answer. I created procedure: ALTER PROCEDURE "DBA"."TECAJ_CESKA1"() url 'https://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.xml' type 'HTTP:GET' When I call this procedure from isql I get this result: Attribute;Value;Instance 'Status';'HTTP/1.1 200 200';'1' 'Body';'\x0a<kurzy banka="CNB" datum="24.03.2021" poradi="58">\x0a<tabulka typ="XML_TYP_CNB_KURZY_DEVIZOVEHO_TRHU">\x0a<radek kod="AUD" mena="dolar" mnozstvi="1" kurz="16,871" zeme="Austrálie"/>\x0a
(25 Mar '21, 02:53)
Tomaz
Replies hidden
1
Do not use a web client procedure but a function (as in the sample) with return type XML that will provide the body as XML document. You can take that as input the the OpenXML operator and extract the desired values. I.e. something like: create or replace function "DBA"."TECAJ_CESKA1"() returns xml url 'https://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.xml' type 'HTTP:GET'; and a sample code block - probably you would need to cast the rate to some kind of numeric type... begin
declare varXml xml;
set varXml = TECAJ_CESKA1();
-- select varXml;
select kod, kurz
from OpenXML(varXml, '//radek')
with (id int '@mp:id',
kod varchar(10) '@kod',
kurz varchar(30) '@kurz')
order by id;
end;
(25 Mar '21, 03:32)
Volker Barth
Thanks Barth! I will try this. Regards Tomaz
(25 Mar '21, 03:53)
Tomaz
2
Suggestion for Tomaz: When Volker says "Here's a FAQ with a similar web call for ECB exchange rates." you should actually go and read that material because it contains exactly the code you need, easily modified for your purposes... create function denni_kurz() returns xml url 'https://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.xml' type 'HTTP:GET' CERTIFICATE 'file=*'; SELECT denni_kurz(); <?xml version="1.0" encoding="UTF-8"?> <kurzy banka="CNB" datum="24.03.2021" poradi="58"> <tabulka typ="XML_TYP_CNB_KURZY_DEVIZOVEHO_TRHU"> <radek kod="AUD" mena="dolar" mnozstvi="1" kurz="16,871" zeme="Austrálie"/> <radek kod="BRL" mena="real" mnozstvi="1" kurz="4,015" zeme="BrazÃlie"/> ... <radek kod="USD" mena="dolar" mnozstvi="1" kurz="22,162" zeme="USA"/> <radek kod="GBP" mena="libra" mnozstvi="1" kurz="30,381" zeme="Velká Británie"/> </tabulka> </kurzy>
(25 Mar '21, 06:04)
Breck Carter
Barth, it looks I still need your help :) Your solution works perfectly, but I would like to get also date value from root element 'kurzy' and also attribut mnozstvi from element 'radek'. I tried to modify your solution in procedure , but I get error: Syntax error or access violation. ALTER PROCEDURE "DBA"."TECAJ_CESKA1_test"()
begin
declare "varXml" xml;
set "varXml" = "TECAJ_CESKA1"();
-- select varXml;
select "kod","mnozstvi",kurz"
from openxml("varXml",'//radek')
with("id" integer '@mp:id',
"kod" varchar(10)'@kod',
"mnozstvi" varchar(10)'@mnozstvi',
"kurz" varchar(30)'@kurz')
order by "id" asc
end
Regards Tomaz
(25 Mar '21, 09:39)
Tomaz
Thanks Breck, I checked his proposal, but it looks I didn't understand it. Sorry! Tomaz
(25 Mar '21, 09:58)
Tomaz
1
Hi Barth, my final working solution is like this: ALTER PROCEDURE "DBA"."TECAJ_CESKA1_test"()
begin
declare "varXml" xml;
set "varXml" = "TECAJ_CESKA2"();
-- select varXml;
select *
from openxml("varXml",'//radek') with(
"datum" varchar(15) '../../@datum' ,
"kod" varchar(10) '@kod',
"kurz" varchar(30) '@kurz',
"mnozstvi" varchar(30) '@mnozstvi'
)
end
//////////////// Thank you for your help! Tomaz
(25 Mar '21, 10:48)
Tomaz
|
