Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

Hello everyone!

I got a task to daily read exchange rate from site:

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 '21, 15:37

Tomaz's gravatar image

accept rate: 0%

edited 24 Mar '21, 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 '21, 16:53

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

converted 25 Mar '21, 03:32

Hi Barth, thanks for answer. I created procedure:

type 'HTTP:GET'

When I call this procedure from isql I get this result:

'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

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
type 'HTTP:GET';

and a sample code block - probably you would need to cast the rate to some kind of numeric type...

   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; 
(25 Mar '21, 03:32) Volker Barth

Thanks Barth! I will try this. Regards Tomaz

(25 Mar '21, 03:53) Tomaz

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 '' 
type 'HTTP:GET' 

SELECT denni_kurz();

<?xml version="1.0" encoding="UTF-8"?>
<kurzy banka="CNB" datum="24.03.2021" poradi="58">
<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"/>
(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.

  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

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

Hi Barth, my final working solution is like this:

  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'


//////////////// Thank you for your help! Tomaz

(25 Mar '21, 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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 24 Mar '21, 15:37

question was seen: 940 times

last updated: 25 Mar '21, 11:54