SQL Server 2k5 - XML query

[crptcblade]

I don't know if this is directly an XML oriented question, or if SQL Server has something to do with it, but here it goes.

I've got my select statement

SELECT Data.query('sum(//Needs/CurrentIncome)') FROM Cache

Where Data is an xml column, and the CurrentIncome nodes of the xml blob all contain numeric data. The problem is that the result of the sum() call is returning the numbers in scientific notation. Which means I can't convert the results to numbers, which is what I really need to do. The results are large numbers, but no where near any sort of numeric data type limit.

So is there something I can do to change the result format?

[mendhak]

Try CAST AS REAL or as a DECIMAL with a large precision (like DECIMAL(50,5)) so that it's 50 places before the decimal and 2 after. I believe you can do the same with NUMERIC(x,y) but it'll give you trailing zeros which you could trim if you need to.

[crptcblade]

SELECT CAST(Data.query('sum(//Needs/CurrentIncome)') AS DECIMAL(38,5)) FROM Cache

Gets me : Explicit conversion from data type xml to decimal is not allowed.

[wild_bill]

You could create a user defined function that accepts an xml parameter, and use something like this:

declare @data xml; set @data = '
<Data>
	<Needs>
		<CurrentIncome>500000</CurrentIncome>
	</Needs>
	<Needs>
		<CurrentIncome>750000</CurrentIncome>
	</Needs>
</Data>'

SELECT sum(INCOME.value('.','money'))
From @Data.nodes('//Needs/CurrentIncome')  as Data(INCOME)