XQuery sum() function

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.

Workaround:

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 SUM( x.y.value('.', 'MONEY') )
FROM @xml.nodes( '/Charges/Charge/Amount' ) x(y)

Don't think you can you sum directly as the amount doesn't meet the rules
for an xs:decimal.

HTH
wBob
"Loren Dummer" wrote:

> 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.
>
>
>