I have to download the XML generated in this page:

https://www.backend-rates.ezv.admin.ch/api/xmldaily?d=20210923&locale=it

The only parameter is the date in YYYYMMDD format.

I know there are some SQL function to do this, but I don't know how to approce the problem.

EDIT: I try this:

           SELECT * FROM XMLTABLE(
  XMLNAMESPACES (DEFAULT 'https://www.backend-rates.ezv.admin.ch/xmldaily',
  'https://www.backend-rates.ezv.admin.ch/api/xmldaily?d=20210922&locale=it' AS "doc" ) ,
  'doc/wechselkurse/devise' PASSING XMLPARSE 
  ( DOCUMENT SYSTOOLS.HTTPGETBLOB                                                                                      
  ('https://www.backend-rates.ezv.admin.ch/dailyrates.xsd' 
        , ''))  
  COLUMNS 
  code Char(3) PATH 'code',
  waehrung char(10) PATH 'waehrung' ,
  kurs decfloat PATH 'kurz'
)
where code = 'USD'

But I obtain an empty result, can you help me find the error?

CodePudding user response：

Multiple problems, listed below the queries because they kill formatting when not below

SELECT * from
    XMLTABLE(
        XMLNAMESPACES (DEFAULT 'https://www.backend-rates.ezv.admin.ch/xmldaily'),
       '$doc/wechselkurse/devise'
        PASSING XMLPARSE 
            (DOCUMENT SYSTOOLS.HTTPGETCLOB('https://www.backend-rates.ezv.admin.ch/api/xmldaily?d=20210922&locale=it', '')) as "doc" 
  COLUMNS 
  code Char(3) PATH '@code',
  waehrung char(10) PATH 'waehrung' ,
  kurs decfloat PATH 'kurs'
)
where code = 'usd'

• You don't need to add your document to namespaces since it's not a namesspace
• What you download and parse is not the document but the schema that can validate it
• You have to give a name to your document in the xpath expression, that's the use of as 'doc' (and not '$doc' like in my previous answer)
• You can refer to that name as $doc in the xpath expression
• code is an attribute, you can get it's value using @code
• code values are lowercase