xml error eurofxref-daily.xml [message #686767] |
Sun, 01 January 2023 05:01 |
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 |
|
Michel Cadot
Messages: 68665 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 |
Solomon Yakobson
Messages: 3275 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 #686773 is a reply to message #686771] |
Sun, 01 January 2023 07:18 |
Solomon Yakobson
Messages: 3275 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 |
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
|
|
|
|