0

I have a view in SQL Server with a mixture of INTEGER, STRING and DECIMAL columns. When I use

SELECT * 
FROM [VIEW] 
FOR XML RAW

the INTEGER and DECIMAL columns get incorrectly converted to a scientific notation value like 2.400000000000000e+001 where it should be simply 24 for the integer column.

If I manually convert the column like 

CAST([column] AS VARCHAR(128))

then this doesn't happen. But what if I only know the name of this one column and the remaining numerical values need to be DECIMAL(32, 10)?

DobleA
  • 334
  • 1
  • 3
  • 13
  • If you don't know the names of the columns that are present in the view, you will have to use Dynamic SQL for that. You could use the query as a basis here: http://stackoverflow.com/questions/12000999/how-to-list-the-source-table-name-of-columns-in-a-view-sql-server-2005 – NickyvV Jun 02 '14 at 20:23
  • 1
    First you say there are integer columns in your view and then you say you have only one integer column and you end with a requirement that the rest should be decimal. Your question is somewhat confusing. So how is it really? What data type does the columns actually have in your view? – Mikael Eriksson Jun 02 '14 at 20:27
  • Sorry for the confusion. I have only one column as INT and the rest columns can be a mixture of STRINGS and DECIMAL(32, 10) – DobleA Jun 02 '14 at 21:16
  • I modified the original question to help make better sense – DobleA Jun 02 '14 at 21:30
  • The only way to avoid this was by actually casting each numeric column inside the SELECT statement of the view. After I SELECT the view, then the columns are correctly formatted. – DobleA Jun 02 '14 at 23:07

0 Answers0