Remove scientific notation from xml column

Question

Post reply

Remove scientific notation from xml column

IT researcher

SSCertifiable

Points: 7600
More actions
May 24, 2013 at 4:21 am

#294335

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 for getting balance i do get it as 1.0002E7 . But i want to get it as 10002000 .
How can i do it?
I have another question. Can i make any changes so that in xml column it self it store as i required without scientific notation.
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

You can cast scientific values as a float; e.g. SELECT CAST('1.0002E7' AS float)

Or even more helpfully from the xml itself like such:

DECLARE @xml xml

SET @xml = '<Root>

<Row>

<Rowid>1</Rowid>

<date>2013-05-06</date>

<Balance>1.0002E7</Balance>

</Row>

</Root>'

SELECT @xml.value('/Root[1]/Row[1]/Balance[1]', 'float')

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

IT researcher SSCertifiable Points: 7600 More actions · Answer 2

Hi Matthew

It worked nice. But can i avoid these scientific notation while saving to xml itself?

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 3

I guess that depends how you're making the xml!

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

IT researcher SSCertifiable Points: 7600 More actions · Answer 4

Hi matthew..

i got it

To change the value in a XML column i need to first extract the value as a float and write it back as an int.

Something like this.

update T

set XMLCol.modify('replace value of (//Balance/text())[1]

with (sql:column("X.Balance") cast as xs:int?)')

from @T as T

cross apply (select @XML.value('(//Balance/text())[1]', 'float')) as X(Balance)

But i wanted decimal not int. Also i want to restrict the number of digits after decimal point. Do you know how it can be done?

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 5

I think my point was more along the lines of that it depends on how you are populating the original xml (eg from a table, from an application etc), or if it's coming from a file that you have no control over for example. I'm also not sure whether you can specify the precision of an xsd:decimal type (see the output below and how it removes the decimal point after the cast!) I'm by no means an xml guru mind, so there may be a way of doing this.

You could try something like the following, but bear in mind that do to the restrictions of updating xml you'd have to do this node by node I think:-

DECLARE @xml xml

DECLARE @Value DECIMAL(18, 2)

DECLARE @ValueASString VARCHAR(255)

SET @xml = '<Root>

<Row>

<Rowid>1</Rowid>

<date>2013-05-06</date>

<Balance>1.0002E7</Balance>

</Row>

</Root>'

SELECT @Value = @xml.value('/Root[1]/Row[1]/Balance[1]', 'float')

SELECT @ValueASString = @Value

SET @xml.modify('replace value of (/Root[1]/Row[1]/Balance[1]/text())[1] with sql:variable("@ValueASString")')

SELECT @xml

SET @xml.modify('replace value of (/Root[1]/Row[1]/Balance[1]/text())[1] with sql:variable("@ValueASString") cast as xs:decimal ?')

SELECT @xml

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

siggemannen SSCrazy Points: 2436 More actions · Answer 6

If you cast the number to numeric when building xml string you'll get rid of sc.notaion

siggemannen SSCrazy Points: 2436 More actions · Answer 7

If you cast the number to numeric when building xml string you'll get rid of sc.notation