CODEWITHSUNDEEP
sql-serverxmlfloating-point
Loser Coder
Loser Coder

Reputation: 2428

SQL 'float' data type, when output as XML, causes undesired float result

You can try simply: table1: has a column1 of type 'float' instead of

SELECT column1 from Table1; gives values as seen in table.

Say this returns 15.1

However, if you try

Select column1 from Table1 
FOR XML PATH('Table1'), Root('SomeRoot'), TYPE

returns: 1.510000000000000e+001

Has anyone seen this, and how was this fixed? thanks in advance :)

Upvotes: 7

Views: 10317

Answers (3)

MAXE
MAXE

Reputation: 5122

It's not necessary to convert the float value into a string to solve this problem. Just convert the float value into a decimal or a numeric type, with the precision you want.

SELECT CAST(column1 AS decimal(38,2))

or

SELECT CAST(column1 AS numeric(18,5))

When then number will be parsed in XML by Sql Server, it won't be in an exponential form.

This approach is obviously faster than a string conversion (that would occur twice).

Upvotes: 1

chezy525
chezy525

Reputation: 4174

Also assuming MSSQL, the str function might fit your needs (MSDN):

select str(column1, 3,1)

Upvotes: 2

Ryan Guill
Ryan Guill

Reputation: 13896

This is what you get when you work with floating point numbers. You can try this though:

SELECT CONVERT(varchar(100), CAST(column1 AS decimal(38,2)))

you will just need to adjust the precision on the decimal to fit your needs.

Upvotes: 10

Related Questions