I have an XML field in SQL Server 2008. Within the XML is a node entitiled "Key". There are some XML fields that will have the same value of 'Key'. I want to count how many duplicates per 'Key'.
For example, if I query :
then the result might be:
I need to get the following output:
If I incorporate the traditional a count/group by with XML then I can't use group by as normal or I get "Cannot use an aggregate or a subquery in an expression used for the group by list
of a GROUP BY clause." :
I've tried variations on the xquery count() function but I keep getting something wrong. Does anyone know what the correct syntax for this scenario would be?
View Complete Post