Remove scientific notation from xml column

  • IT researcher

    SSCertifiable

    Points: 7463

    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

  • Matthew Darwin

    SSCertifiable

    Points: 5513

    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: 7463

    Hi Matthew

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

  • Matthew Darwin

    SSCertifiable

    Points: 5513

    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: 7463

    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

    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: 2223

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

  • siggemannen

    SSCrazy

    Points: 2223

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

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

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