Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 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 May 24 '13 at 17:17
add comment

2 Answers

up vote 3 down vote accepted

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.

share|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
add comment

You can use the convert function, like so :

DECLARE @x XML = 
'<Root>
  <Row>
    <Rowid>1</Rowid>
    <date>2013-05-06</date>
    <Balance>1.0002E7</Balance>
  </Row>
</Root>'

SELECT CONVERT(FLOAT,(@x.value('(/Root/Row/Balance)[1]','nvarchar(100)')))
share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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