on 2021 Mar 24 3:37 PM
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
Request clarification before answering.
I don't have a sample available but some hints/keywords:
Here's a FAQ with a similar web call for ECB exchange rates.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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';'\ <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"/>\
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;
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>
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
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
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.