XQuery in SQL server doing SUM over zero value

Question

I'm trying to extract monetary sums stored in some poorly formated xml columns (there is no schema defined for the XML column which I guess is part of the problem). I'm getting a conversion error whenever I encounter a node with 0 as its value.

Example:

select xml.value('sum(/List/value)', 'numeric') sum
from (select cast('<List><value>1</value><value>2</value></List>' as xml) xml) a

gives the sum 3 while:

select xml.value('sum(/List/value)', 'numeric') sum
from (select cast('<List><value>0</value><value>0</value></List>' as xml) xml) a

raises the error: "Error converting data type nvarchar to numeric."

Any idea how I can make my query return 0 when summing up a list of zero-valued nodes?

Answer 1

Your comment suggests an answer to your problem.

Instead of converting to numeric, convert to float. Scientific notation will convert to float.

Answer 2

you can also use and if statement like this:

@x.value('if (sum(/List/value) = 0) then 0 else sum(/List/value)', 'numeric')