I am having problems getting a sum from xml.
The following works and returns - 1150.75
DECLARE @Xml xml
SELECT @Xml = '<Charges>
<Charge>
<Amount>100.00</Amount>
</Charge>
<Charge>
<Amount>50.50</Amount>
</Charge>
<Charge>
<Amount>1000.25</Amount>
</Charge>
</Charges>'
SELECT @Xml.query('
sum( /Charges/Charge/Amount )
')
Now take the amount 1000.25 and change it to 1,000.25. It will return
150.5.
DECLARE @Xml xml
SELECT @Xml = '<Charges>
<Charge>
<Amount>100.00</Amount>
</Charge>
<Charge>
<Amount>50.50</Amount>
</Charge>
<Charge>
<Amount>1,000.25</Amount>
</Charge>
</Charges>'
SELECT @Xml.query('
sum( /Charges/Charge/Amount )
')
How can I get Sql Server to give me the correct sum of 1150.75 when some
values have commas in the values?
Thank-you in advance.