troubleshooting Question

SQL Server, (FOR) XML, REAL Rounding Error

Avatar of Thejaka
ThejakaFlag for Sri Lanka asked on
Microsoft SQL Server 2005
6 Comments1 Solution823 ViewsLast Modified:
This happens to be my fist question on EE on a technical matter.
I asked before [23613318] but got no solution, so deleted it, and am asking again...

Try below snippet in SQL Sever 2005 Query Window... Note that the 3 subsequent queries all select the exact values (on at a time) as the initial query, but the results are returned with a rounding error... No arithmetic operations are supposed to be invoked. I'm at a loss to understand why... I expect I may be able to figure out what's going on if I dig into the SQL Server and XML Parser internals, but thought I'd pose the problem here hoping somebody might already have encountered the issue, and/or might have an idea what might cause this.

I could always get this rounded, so I'm not looking for a solution exactly, only looking for a better one, and trying to understand what's going on inside.
The main reason I'm not trying to round the numbers is that I oughtn't have to... I'm not doing any arithmetic, and I'm passing in nice REAL numbers, so I can't see why the XML processor has to meddle with my numbers and muddle them up...

/* :) */
WITH T(A,B,C)AS(SELECT CAST(45.1AS REAL),CAST(45.11AS REAL),CAST(45.12AS REAL))
SELECT * FROM T;
 
/* :# */
WITH T(A,B,C)AS(SELECT CAST(45.1AS REAL),CAST(45.11AS REAL),CAST(45.12AS REAL))
SELECT A FROM T
FOR XML RAW;
 
/* :# */
WITH T(A,B,C)AS(SELECT CAST(45.1AS REAL),CAST(45.11AS REAL),CAST(45.12AS REAL))
SELECT B FROM T
FOR XML RAW;
 
/* :# */
WITH T(A,B,C)AS(SELECT CAST(45.1AS REAL),CAST(45.11AS REAL),CAST(45.12AS REAL))
SELECT C FROM T
FOR XML RAW;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros