How to use sum and count aggregate functions with xml data type in SQL Server 2005

Hi:
I have xml in sql server 2005 beta2 that is stored as an xml data type and I'm trying to run some aggregate functions like count and sum, but I don't know how to do it. The elements are numeric values, but 'sum' and also 'count' return errors. I have included the error message and the sp below. Any ideas how to use sum and count correctly?

Thanks,

Paul
Msg 8117, Level 16, State 1, Procedure get_vID_FormXML_summary, Line 11

Operand data type varchar is invalid for sum aggregate operator.

--

alter procedure dbo.get_vID_FormXML_summary

@.ID int

as

SELECT FormXML.query(sum('/transactionAmounts/transactionShares/value'))

AS Result

FROM _Company_Info_01_FLAT

WHERE (ID = @.ID)

--GROUP BY DateFiled, FormXML

This looks like a typing issue. Is the XML datatype typed with a schema or untyped? You may also upgrade to RTM (or at least the Sept CTP). The behaviour and error messages have been vastly improved since Beta2. Best regards Michael <kloepper@.discussions.microsoft.com> wrote in message news:032b5300-6b08-4886-8440-fcf708316b00_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi:I have xml in sql server 2005 beta2 that is stored as an xml data type and I'm trying to run some aggregate functions like count and sum, but I don't know how to do it. The elements are numeric values, but 'sum' and also 'count' return errors. I have included the error message and the sp below. Any ideas how to use sum and count correctly?Thanks,PaulMsg 8117, Level 16, State 1, Procedure get_vID_FormXML_summary, Line 11 Operand data type varchar is invalid for sum aggregate operator. --alter procedure dbo.get_vID_FormXML_summary @.ID int as SELECT FormXML.query(sum('/transactionAmounts/transactionShares/value')) AS Result FROM _Company_Info_01_FLAT WHERE (ID = @.ID) --GROUP BY DateFiled, FormXML|||I checked the version again, it is 9.00.1116.00, that might be the Sept CTP.

Anyway, the xml has no schema associted with it, except the dbo schema.

So, what do you suggest now?

Thanks,

Paul|||

Hi.

Subquery or User defined aggregate.
The subquery is easiest:

-- Snippet

USE adventureworks

GO

WITH SubQ AS (

SELECT LineTotal AS Num FROM Sales.SalesOrderDetail

)

Select Sum(Num) FROM SubQ
-- EoSnippet

Of course, LineTotal should be replaced with some XML stuff and a conversion...

Hope this helps

|||Actually, it looks like the 'conversion' (I assume that you mean the type casting error) is what I need help with.

I get the following error using the subquery method...this looks like the error that I got with the first method i tried previously.

So how do I cast this properly? Any literature on the subject?

Thanks,
Paul

USE SEC

GO
WITH SubQ
AS
(SELECT FormXML
AS '/transactionAmounts/transactionShares/value'
FROM _Company_Info_01_FLAT)
SELECT SUM('/transactionAmounts/transactionShares/value')
AS
SumOfValues
FROM SubQ
AS
SubQ_1

Operand data type varchar is invalid for sum aggregate operator.

|||You should use the XML value() method rather than query(). query() returns an XML datatype instance, which as the error implies is not compatible with SUM(). value(), on the other hand, will return a "regular" scalar SQL type.

In your case, you should rewrite the query to be:

SELECT SUM(FormXML.value('(/transactionAmounts/transactionShares/value/text())[1]', 'int'))
AS Result

If you have multiple <value /> tags within a single instance that you are trying to get the sum of, then you can use the XQuery sum() function:

SELECT SUM(FormXML.value('sum(/transactionAmounts/transactionShares/value/text())', 'int'))
AS Result

In the second case, note that the "sum" is within the string literal that is our XQuery expression, because we are invoking the XQuery sum aggregate.

|||

You have two ways to do sums: You can use the XPath/XQuery sum() function inside an XQuery expression that you call through either the query() or as John mentions through the value() method (if you want to cast the value to a SQL value). Also, it depends on whether you want to do the sum over all elements or only all elements within a row.

In your example, you are mixing the metaphors.

And build 9.00.1116.00 is much older than the September CTP (probably the December 04 CTP).

Try the following:

create table SumT(x xml)
go

insert into SumT
select N'<a><b>1</b><b>2</b></a>'
union
select N'<a><b>3</b><b>4</b></a>'
go

select SUM(B.n.value('.', 'decimal'))
from SumT cross apply x.nodes('/a/b') B(n)

select SUM(x.value('sum(/a/b)', 'decimal'))
from SumT

select x.value('sum(/a/b)', 'decimal')
from SumT

Best regards

Michael

|||I want the sum of multiple rows, so this one below works for me.

select SUM(B.n.value('.', 'decimal'))
from SumT cross apply x.nodes('/a/b') B(n)

It does, however, give slightly different results than the first method that John demonstrated. Your second and third method, as well as John's second method return casting errors.

I feel as though I'm looking for a needle in a haystack in trying to find answers looking through the MSDN documentation. Is that the best place, or can you suggest another source or good book that includes the subject of writing xml queries in SQL Server 2005. Perhaps I just need to take a closer look at the MSDN explanations?

Thanks for your help,

Paul
|||John:

Thanks for the help. I'm beginning to understand this a little better, but it's going to take some more work.

I'm just wondering if you have any favorite books or web sites to learn xqueries for SQL Server 2005? I've just been using MSDN2, but it seems to be a struggle to find what I'm looking for.

Paul|||Dear Paul

First, both John's and my queries should work. E.g., the expression I posted I copied from my management studio window after I made sure that they would give me results. If you see errors just running the queries above, I strongly recommend that you upgrade to the September CTP or the RTM version.

Regarding books, and other resources: I recommend our team's weblogs (you can start with mine at http://www.sqljunkies.com/weblog and follow the links to others).

We also have an XQuery whitepaper on MSDN.microsoft.com (linked from me weblog) and the latest versions of Book Online also have quite a bit about the XQuery support.

I hope this helps
Michael