Home » SQL & PL/SQL » SQL & PL/SQL » xml error eurofxref-daily.xml (19c)
xml error eurofxref-daily.xml [message #686767] Sun, 01 January 2023 05:01 Go to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I have been reckoning my brains over xml parsing and without any successfull.
Does anybody please know to correct write a sql to work fine ??

The source of xml is from website => https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

I inserted the xml into table T (xmltype datatype):

SELECT EXTRACTVALUE ( value ( tab ), '/ Cube / @ currency',
                                  'xmlns = "http://www.ecb.int/vocabulary/2002-08-01/eurofxref') currency,
       EXTRACTVALUE ( value ( tab ), '/ Cube / @ rate',
                                  'xmlns = "http://www.ecb.int/vocabulary/2002-08-01/eurofxref') rate
  FROM t, TABLE ( XMLSEQUENCE ( EXTRACT (x,
'/gesmes: Envelope / Cube / Cube',
'xmlns: gesmes = http://www.gesmes.org/xml/2002-08-01 xmlns = "http://www.ecb.int/vocabulary/2002-08-01/eurofxref"'
) ) ) tab;
Or without inserting into table:
SELECT EXTRACTVALUE ( value ( tab ), '/ Cube / @ currency',
                                  'xmlns = "http://www.ecb.int/vocabulary/2002-08-01/eurofxref') currency,
       EXTRACTVALUE ( value ( tab ), '/ Cube / @ rate',
                                  'xmlns = "http://www.ecb.int/vocabulary/2002-08-01/eurofxref') rate
  FROM TABLE ( XMLSEQUENCE ( EXTRACT (
  '<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2022-12-30">
<Cube currency="USD" rate="1.0666"/>
<Cube currency="JPY" rate="140.66"/>
</Cube>
</Cube>
</gesmes:Envelope>' ,
'/gesmes: Envelope / Cube / Cube',
'xmlns: gesmes = http://www.gesmes.org/xml/2002-08-01 xmlns = "http://www.ecb.int/vocabulary/2002-08-01/eurofxref"'
) ) ) tab;
Nothing works fine.
Do you have any ide how to do that ??

thanks a lot

Regards
martin

[Updated on: Sun, 01 January 2023 05:03]

Report message to a moderator

Re: xml error eurofxref-daily.xml [message #686768 is a reply to message #686767] Sun, 01 January 2023 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2    select
  3  '<gesmes:Envelope>
  4    <gesmes:subject>Reference rates</gesmes:subject>
  5    <gesmes:Sender>
  6      <gesmes:name>European Central Bank</gesmes:name>
  7    </gesmes:Sender>
  8    <Cube>
  9      <Cube time="2022-12-30">
 10        <Cube currency="USD" rate="1.0666"/>
 11        <Cube currency="JPY" rate="140.66"/>
 12        <Cube currency="BGN" rate="1.9558"/>
 13        <Cube currency="CZK" rate="24.116"/>
 14        <Cube currency="DKK" rate="7.4365"/>
 15        <Cube currency="GBP" rate="0.88693"/>
 16        <Cube currency="HUF" rate="400.87"/>
 17        <Cube currency="PLN" rate="4.6808"/>
 18        <Cube currency="RON" rate="4.9495"/>
 19        <Cube currency="SEK" rate="11.1218"/>
 20        <Cube currency="CHF" rate="0.9847"/>
 21        <Cube currency="ISK" rate="151.50"/>
 22        <Cube currency="NOK" rate="10.5138"/>
 23        <Cube currency="HRK" rate="7.5365"/>
 24        <Cube currency="TRY" rate="19.9649"/>
 25        <Cube currency="AUD" rate="1.5693"/>
 26        <Cube currency="BRL" rate="5.6386"/>
 27        <Cube currency="CAD" rate="1.4440"/>
 28        <Cube currency="CNY" rate="7.3582"/>
 29        <Cube currency="HKD" rate="8.3163"/>
 30        <Cube currency="IDR" rate="16519.82"/>
 31        <Cube currency="ILS" rate="3.7554"/>
 32        <Cube currency="INR" rate="88.1710"/>
 33        <Cube currency="KRW" rate="1344.09"/>
 34        <Cube currency="MXN" rate="20.8560"/>
 35        <Cube currency="MYR" rate="4.6984"/>
 36        <Cube currency="NZD" rate="1.6798"/>
 37        <Cube currency="PHP" rate="59.320"/>
 38        <Cube currency="SGD" rate="1.4300"/>
 39        <Cube currency="THB" rate="36.835"/>
 40        <Cube currency="ZAR" rate="18.0986"/>
 41      </Cube>
 42    </Cube>
 43  </gesmes:Envelope>' data
 44    from dual)
 45  select currency, rate
 46  from data,
 47       xmltable ('/Envelope/Cube/Cube/Cube' passing xmltype(replace(data,'gesmes:',''))
 48                 columns
 49                   currency varchar2(8) path '//@currency',
 50                   rate     number      path '//@rate')
 51  /
CURRENCY       RATE
-------- ----------
USD          1.0666
JPY          140.66
BGN          1.9558
CZK          24.116
DKK          7.4365
GBP          .88693
HUF          400.87
PLN          4.6808
RON          4.9495
SEK         11.1218
CHF           .9847
ISK           151.5
NOK         10.5138
HRK          7.5365
TRY         19.9649
AUD          1.5693
BRL          5.6386
CAD           1.444
CNY          7.3582
HKD          8.3163
IDR        16519.82
ILS          3.7554
INR          88.171
KRW         1344.09
MXN          20.856
MYR          4.6984
NZD          1.6798
PHP           59.32
SGD            1.43
THB          36.835
ZAR         18.0986

31 rows selected.

[Updated on: Sun, 01 January 2023 06:39]

Report message to a moderator

Re: xml error eurofxref-daily.xml [message #686769 is a reply to message #686767] Sun, 01 January 2023 06:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
with sample as (
                select xmltype('<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2022-12-30">
<Cube currency="USD" rate="1.0666"/>
<Cube currency="JPY" rate="140.66"/>
</Cube>
</Cube>
</gesmes:Envelope>') xmldoc from dual
              )
select  x.*
  from  sample s,
        xmltable(
                 xmlnamespaces (
                                'http://www.gesmes.org/xml/2002-08-01' as "gesmes",
                                default 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref'
                               ),
                 '/gesmes:Envelope/Cube/Cube/Cube'
                 passing s.xmldoc
                 columns
                 currency varchar2(10) path '@currency',
                 rate     number       path '@rate'
                ) x
/

CURRENCY         RATE
---------- ----------
USD            1.0666
JPY            140.66

SQL>
SY.
Re: xml error eurofxref-daily.xml [message #686770 is a reply to message #686769] Sun, 01 January 2023 06:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel,

In general using something like xmltype(replace(data,'gesmes:','')) isn't kosher and can cause issues if XML has same tags in multiple namespaces. Why not simply use namespaces clause like I did?

SY.

[Updated on: Sun, 01 January 2023 07:04]

Report message to a moderator

Re: xml error eurofxref-daily.xml [message #686771 is a reply to message #686770] Sun, 01 January 2023 06:55 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Solomon, excelent work, thanks a lot. Your SQL runs perfect.

Michel, if I put the exact XML code into your SQL the result return nothing.
The different is XML begins with
<gesmes:Envelope xmlns ...
And your XML begins with
<gesmes:Envelope> ...

[Updated on: Sun, 01 January 2023 06:58]

Report message to a moderator

Re: xml error eurofxref-daily.xml [message #686773 is a reply to message #686771] Sun, 01 January 2023 07:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Reason Michel's code didn't work is default namespace he excluded from his sample:

with sample as (
                select '<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2022-12-30">
<Cube currency="USD" rate="1.0666"/>
<Cube currency="JPY" rate="140.66"/>
</Cube>
</Cube>
</gesmes:Envelope>' data from dual
              )
select  x.*
  from  sample s,
        xmltable(
                 '/Envelope/Cube/Cube/Cube'
                 passing xmltype(replace(data,'gesmes:',''))
                 columns
                 currency varchar2(10) path '//@currency',
                 rate     number       path '//@rate'
                ) x
/

no rows selected

SQL>
We still have to add it:

with sample as (
                select '<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2022-12-30">
<Cube currency="USD" rate="1.0666"/>
<Cube currency="JPY" rate="140.66"/>
</Cube>
</Cube>
</gesmes:Envelope>' data from dual
              )
select  x.*
  from  sample s,
        xmltable(
                 xmlnamespaces (
                                default 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref'
                               ),
                 '/Envelope/Cube/Cube/Cube'
                 passing xmltype(replace(data,'gesmes:',''))
                 columns
                 currency varchar2(10) path '//@currency',
                 rate     number       path '//@rate'
                ) x
/

CURRENCY         RATE
---------- ----------
USD            1.0666
JPY            140.66

SQL>
So we should provide all namespaces instead of editing XML since we have to have namespaces clause anyway.

SY.
Re: xml error eurofxref-daily.xml [message #686774 is a reply to message #686773] Sun, 01 January 2023 07:45 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

Thanks for excelent work, I really appreciate it.

The last question, If I would like to do this by Oracle ODI (means create xml topology),
do you know any forum (or any help) ?

Martin
Re: xml error eurofxref-daily.xml [message #686775 is a reply to message #686774] Sun, 01 January 2023 07:57 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Sorry, I have no ODI experience.

SY.
Previous Topic: Parent/Child Table Design Help
Next Topic: COALESCE and CASE
Goto Forum:
  


Current Time: Thu Mar 28 16:50:53 CDT 2024