Remove scientific notation from an XML column I have a XML column as shown below:

&lt;Root&gt;
  &lt;Row&gt;
    &lt;Rowid&gt;1&lt;/Rowid&gt;
    &lt;date&gt;2013-05-06&lt;/date&gt;
    &lt;Balance&gt;1.0002E7&lt;/Balance&gt;
  &lt;/Row&gt;
&lt;/Root&gt;


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?
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.