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

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

• JasonChris

  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
  
  

• hjb3

  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
  
  

• gontran123

  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.

  
  
  

• David Dong

  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.

  
  

• Akiwa

  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

  
  
  

• Jay Hickerson MS

   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
  
  
  

• mmadhuso

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

  
  

• nonno

  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

  
  
  

• Derek Dabeach

  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
  
  
• How to use sum and count aggregate functions with xml data type in SQL Server 2005