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

asked 24 Mar, 15:37

Tomaz's gravatar image

Tomaz
86227
accept rate: 0%

edited 24 Mar, 15:38


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.

permanent link

answered 24 Mar, 16:53

Volker%20Barth's gravatar image

Volker Barth
37.4k343510776
accept rate: 34%

converted 25 Mar, 03:32

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, 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, 03:32) Volker Barth

Thanks Barth! I will try this. Regards Tomaz

(25 Mar, 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, 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, 09:39) Tomaz

Thanks Breck, I checked his proposal, but it looks I didn't understand it. Sorry! Tomaz

(25 Mar, 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, 10:48) Tomaz
showing 1 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×23

question asked: 24 Mar, 15:37

question was seen: 124 times

last updated: 25 Mar, 11:54