T-SQL - how can I use group by on xml objects

Question

I've wrote the following query which I expect to return a data-set as outlined under the query

Query

SELECT 
RelatedRecordID AS [OrganisationID], 
Data.value('(//OpportunityViewEvent/Title)[1]','nvarchar(255)') AS OpportunityTitle,
Data.value('(//OpportunityViewEvent/ID)[1]','int') AS OpportunityID,
Count(Data.value('(//OpportunityViewEvent/ID)[1]','int')) AS Visits
FROM [Audit].[EventData]
LEFT OUTER JOIN Employed.Organisation AS ORG ON [EventData].RelatedRecordID = ORG.ID
Where EventTypeID = 4
Group BY RelatedRecordID
Order By Visits Desc

Expected Result

+-----------------+-----------------+---------------+--------+
| OrganisationID  | OpportunityTitle | OpportunityID | Visits |  
+-----------------+------------------+---------------+--------+
|              23 | Plumber          |           122 |    567 |  
|              65 | Accountant       |            34 |    288 | 
|              12 | Developer        |            81 |    100 | 
|              45 | Driver           |            22 |     96 | 
+-----------------+------------------+---------------+--------+

I receive an error saying

Column 'Audit.EventData.Data' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I then try to group the xml data I get a different error saying

XML methods are not allowed in a GROUP BY clause.

Is there a way to work around this?

Thanks

SELECT is processed after GROUP BY. If you want to group the results of select you should use a subquerey, eg SELECT ... FROM (SELECT ... FROM EventData) x GROUP By ... — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 7, 2016 at 15:11

Kannan Kandasamy · Accepted Answer · 2016-09-07 15:15:13Z

2

You can do by adding it into CTE

;with cte as (
SELECT 
 RelatedRecordID AS [OrganisationID], 
 Data.value('(//OpportunityViewEvent/Title)[1]','nvarchar(255)') AS   OpportunityTitle,
 Data.value('(//OpportunityViewEvent/ID)[1]','int') AS OpportunityID,
 Data.value('(//OpportunityViewEvent/ID)[1]','int') as visit
FROM [Audit].[EventData]
LEFT OUTER JOIN Employed.Organisation AS ORG ON [EventData].RelatedRecordID = ORG.ID
Where EventTypeID = 4 )
select OrganisationID, opportunityTitle, opportunityId,  count(visit) as Visits from cte 
Group BY OrganisationID, opportunityTitle, opportunityId

answered Sep 7, 2016 at 15:15

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL - how can I use group by on xml objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related