I have a XML column as shown below:


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?

  • 2
    Interestingly you have the same post on SSC but glad that you post the answer there are well .. sqlservercentral.com/Forums/Topic1456367-391-1.aspx#bm1456385 – Kin Shah May 24 '13 at 14:54
  • 1
    @Kin Ha. Maybe I should post my updated answer there also. Littering the internet with complicated XML queries. – Mikael Eriksson May 24 '13 at 17:14
  • @MikaelEriksson its your answer ... I thought that it should be referenced .. so you can get the credit. – Kin Shah May 24 '13 at 17:17

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.

| improve this answer | |
  • Nice it worked.But if i want it to cast to decimal then what should i use? Also can i set the decimal with number of digits after decimal point? – IT researcher May 24 '13 at 13:58
  • @ITresearcher I have updated the answer. – Mikael Eriksson May 24 '13 at 15:05
  • SQL Fiddle, nice! – DiGi May 30 '13 at 20:26

You can use the convert function, like so :


SELECT CONVERT(FLOAT,(@x.value('(/Root/Row/Balance)[1]','nvarchar(100)')))
| improve this answer | |
  • Ok.but can i avoid having these scientific notation in the xml itself? – IT researcher May 24 '13 at 11:17
  • That just depends on the input really, you have to have a look at the application code that's inserting the XML into the field. – druzin May 24 '13 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.