SQL Server Xquery, cast to decimal with localization it-IT

Question

I have a list of decimal values in XML formatted with italian localization (that is comma as decimal separator and dot as thousands separator) and I have to convert to decimal values.

The XML is like this:

<ROW>
  <COSTO>2</COSTO>
  <COSTO>3,6</COSTO>
  <COSTO>1.005,10</COSTO>
</ROW>

When are extracted, the decimal numbers will be:

2
3.6
1005.10

I'm using REPLACE function to remove dots and convert commas to dots, before useing CONVERT function:

CONVERT(decimal(10,2), REPLACE(REPLACE(
  XMLDATA.value('(//COSTO)[1]','nvarchar(20)')
  , '.',''), ',','.'))

Is there any syntax to make Xquery read the value using localization, to get the value as decimal?

Something like this (this is not working):

XMLDATA.value('(//COSTO)[1] with local it-IT','decimal(10,2)')

Thanks

Answer 1

If there are commas in the value, you can convert to MONEY first， For example:

DECLARE @xml XML
SET @xml='<ROW>
          <COSTO>2</COSTO>
          <COSTO>3,6</COSTO>
          <COSTO>1.005,10</COSTO>
</ROW>' 
SELECT  CASE WHEN ISNUMERIC(t.v)=1 THEN CONVERT(decimal(10,2),REPLACE(REPLACE(t.v,'.',''),',','.') ) ELSE 0 END    
FROM @xml.nodes('ROW/COSTO') s(b)
CROSS APPLY(VALUES(s.b.value('.','varchar(100)'))) t(v)

---------------------------------------
2.00
3.60
1005.10

If your server version is up 2012, you can use PASE function

 SELECT  PARSE(t.v AS MONEY USING 'it-IT')
 FROM @xml.nodes('ROW/COSTO') s(b)
 CROSS APPLY(VALUES(s.b.value('.','varchar(100)'))) t(v)