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

i've wrote following query expect return data-set outlined under query

query

select  relatedrecordid [organisationid],  data.value('(//opportunityviewevent/title)[1]','nvarchar(255)') opportunitytitle, data.value('(//opportunityviewevent/id)[1]','int') opportunityid, count(data.value('(//opportunityviewevent/id)[1]','int')) visits [audit].[eventdata] left outer join employed.organisation org on [eventdata].relatedrecordid = org.id eventtypeid = 4 group relatedrecordid order 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 error saying

column 'audit.eventdata.data' invalid in select list because not contained in either aggregate function or group clause.

if try group xml data different error saying

xml methods not allowed in group clause.

is there way work around this?

thanks

you can adding cte

;with cte ( select   relatedrecordid [organisationid],   data.value('(//opportunityviewevent/title)[1]','nvarchar(255)')   opportunitytitle,  data.value('(//opportunityviewevent/id)[1]','int') opportunityid,  data.value('(//opportunityviewevent/id)[1]','int') visit [audit].[eventdata] left outer join employed.organisation org on [eventdata].relatedrecordid = org.id eventtypeid = 4 ) select organisationid, opportunitytitle, opportunityid,  count(visit) visits cte  group organisationid, opportunitytitle, opportunityid