Skip to Main Content
  • Questions

  • Problem with loading data from XML decimal point with german nls-settings

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Wilfried.

Asked: November 06, 2025 - 12:36 pm UTC

Last updated: November 13, 2025 - 2:54 pm UTC

Version: VM with Appliance Oracle_Database_23c_Free_Developer_Appliance.ova

Viewed 1000+ times

You Asked 

-- 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 -- ok


Shouldnt all values be treated the same way?

and Chris said...

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

Rating

  (3 ratings)

Comments

So its a bug

Wilfried Eigl, November 07, 2025 - 4:28 am UTC

Thanks for your response.
The data is created by a GPS device. Of course we can replace all ".0" values with null.
I just wanted to be sure that the problem is the database and not my stupidity.

Chris Saxon
November 07, 2025 - 10:17 am UTC

Yeah, it's not right.

Do you need to use German NLS settings for these queries? They work if you use a period for the decimal separator instead of a comma for the session-level NLS_NUMERIC_CHARACTERS.

Wrong expectation, I think

mathguy, November 08, 2025 - 5:24 am UTC

XML has no intrinsic concept of "number", it only deals with strings. So, it shouldn't be sensitive to numeric character settings. You are not referencing a schema that would somehow indicate that the content of ele tags should be numeric. The values are just strings of characters - the fact that we may view them as numbers is irrelevant. And you are extracting the values as VARCHAR2 - so there should be no implicit conversion anywhere. The output should look exactly like the input. The 342.0 is actually the only correct one in your output! There is no reason why, in text (strings of characters), the period should become a comma.

This is exactly how the whole thing (data creation plus query) works in Oracle 12 and 19 (12.2.0.1 and 19.3.0.0.0 respectively). That is, even with numeric characters set to "decimal comma" and "period as group separator", the query returns the strings exactly as they were input - there is no conversion of 340.8 to 340,8. The period remains a period, as it should. These are strings, not numbers!

I am able to reproduce exactly the behavior you reported on Oracle 23.6.0.24.10, after I change the numeric characters to "decimal comma" and "period separator". This is, then, a bug INTRODUCED in version 23, it did not exist in earlier versions.

If you check what is stored in the table (without the XMLTABLE call), you will see that the translation of period to comma has already happened. The bug is in the storing of XML data, not in the query. Which, in particular, means that you couldn't even extract the values, convert them to numbers, and then back to strings - conversion to number will fail even if you give explicit NLS_NUMERIC_CHARACTERS arguments, because the inputs sometimes have decimal point and sometimes decimal comma!

Finally, if you use a WITH clause to provide the data on the fly (instead of storing it), the query will work correctly even in Oracle 23, returning the strings UNCHANGED. The bug is not introduced in the XMLTYPE constructor, but rather in some weird processing while XML data is being stored.
Chris Saxon
November 13, 2025 - 2:54 pm UTC

Good points mathguy

So its a bug

A reader, November 15, 2025 - 8:33 am UTC

with alter session set nls_numeric_characters='.,'; everything is fine.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.