-- Settings
select banner from v$version;
--Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
select * from v$nls_parameters where parameter in ('NLS_NUMERIC_CHARACTERS','NLS_TERRITORY','NLS_LANGUAGE');
-- NLS_LANGUAGE GERMAN 3
-- NLS_TERRITORY GERMANY 3
-- NLS_NUMERIC_CHARACTERS ,. 3
create table asktom_gpx_tab
(i number
,xmldata xmltype
);
INSERT INTO asktom_gpx_tab
-- (i,xmldata)
VALUES (1
,xmltype(
'<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<gpx>
<trkpt lat="49.773210" lon="8.930600"><ele>340.8</ele></trkpt>
<trkpt lat="49.772980" lon="8.930280"><ele>342.0</ele></trkpt>
<trkpt lat="49.772740" lon="8.929900"><ele>341.5</ele></trkpt>
</gpx>')
);
commit;
select elevation
from asktom_gpx_tab t,
xmltable('/gpx/trkpt' passing t.xmldata
columns elevation varchar2(20) path 'ele') x1;
-- 340,8 -- converted because NLS sets decimal sign to comma
-- 342.0 -- why is this value not converted to comma???
-- 341,5 -- converted because NLS sets decimal sign to comma
-- I found a Question here with a supposed solution but it doesnt work right
select to_number(elevation,'FM9999D99','nls_numeric_characters = '',.''')
from asktom_gpx_tab t,
xmltable('/gpx/trkpt' passing t.xmldata
columns elevation varchar2(20) path 'ele') x1;
-- 340,8 -- ok
-- 3420 -- This is definitely a wrong value
-- 341,5 -- okShouldnt all values be treated the same way?
I'm not sure why this is happening either. I've raised it internally.
This appears to affect numbers that only have zero after the decimal point. If you can omit these trailing zeros in the values they're processed correctly:
insert into asktom_gpx_tab
(i,xmldata)
values (1
,xmltype(
'<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<gpx>
<trkpt lat="49.773210" lon="8.930600"><ele>340.8</ele></trkpt>
<trkpt lat="49.772980" lon="8.930280"><ele>342</ele></trkpt>
<trkpt lat="49.772740" lon="8.929900"><ele>341.5</ele></trkpt>
</gpx>')
);
commit;
select elevation
from asktom_gpx_tab t,
xmltable('/gpx/trkpt' passing t.xmldata
columns elevation varchar2(20) path 'ele') x1;
ELEVATION
--------------------
340,8
342
341,5
select to_number(elevation,'FM9999D99','nls_numeric_characters = '',.''') converted
from asktom_gpx_tab t,
xmltable('/gpx/trkpt' passing t.xmldata
columns elevation varchar2(20) path 'ele') x1;
CONVERTED
----------
340,8
342
341,5