XQuery sum() function

#11

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:

Quote:

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.

#12

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:

Quote:

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.

#13

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:

Quote:

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

XQuery sum() function

microsoft.public.sqlserver.xml microsoft.public.sqlserver.xml

RE: XQuery sum() function - 12-03-2008 , 05:40 AM

RE: XQuery sum() function - 12-03-2008 , 05:40 AM

RE: XQuery sum() function - 12-03-2008 , 05:40 AM