Sql-server – Remove scientific notation from an XML column

castsql serverxquery

I have a XML column as shown below:

<Root>
  <Row>
    <Rowid>1</Rowid>
    <date>2013-05-06</date>
    <Balance>1.0002E7</Balance>
  </Row>
</Root>

When I query to get the balance, I get it as 1.0002E7. But I want to get it like this: 10002000.

How can I do it?

Can I make any changes so that the XML column itself is stored as I want it without scientific notation?

Best Answer

Specify float when fetching the value from the XML.

select @XML.value('(//Balance)[1]', 'float');

To change the value in a XML column with decimals included you need to first extract the value as a float and write it back as an numeric with the appropriate precision and scale.

Something like this.

update T 
set XMLCol.modify('replace value of (//Balance/text())[1] 
                   with (sql:column("X.Balance"))')
from YourTable as T
  cross apply (select convert(numeric(18,4), T.XMLCol.value('(//Balance/text())[1]', 'float'))) as X(Balance)

SQL Fiddle

The reason for the scientific notation is because it is the way SQL Server handles xs:float when using untyped XML.

From Type Casting Rules in XQuery

When casting a value of type xs:float or xs:double, or any one of their subtypes, to a string or untypedAtomic type, the value is represented in scientific notation. This is done only when the value's absolute value is less than 1.0E-6, or greater than or equal to 1.0E6. This means that 0 is serialized in scientific notation to 0.0E0.

Related Question