Menu
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?
893
The SUM() function calculates the sum of a set of values. Note: NULL values are ignored.
The SUM() function returns the total sum of a numeric column.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; If you need to arrange the data into groups, then you can use the GROUP BY clause.
Your comment suggests an answer to your problem.
Instead of converting to numeric, convert to float. Scientific notation will convert to float.
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
