cancel
Showing results for 
Search instead for 
Did you mean: 

Read XML file from web site

1,158

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

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor

I don't have a sample available but some hints/keywords:

  • Web client functions/procedures do the actual web request and provide the response.
  • You will usually need to use OpenXML() to work on the result from the web client call to fetch the desired values.

Here's a FAQ with a similar web call for ECB exchange rates.

0 Kudos

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"/>\
VolkerBarth
Contributor

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;
0 Kudos

Thanks Barth! I will try this. Regards Tomaz

Breck_Carter
Participant

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>
0 Kudos

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

0 Kudos

Thanks Breck, I checked his proposal, but it looks I didn't understand it. Sorry! 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