select an xml type column in select query with group by SQL Server 2008

Question

I am writing a select query in which I am fetching several columns (by joining 3-4 tables). I use group by clause to group my results.

Query -

    select ci.Candidate_Id, ci.FirstName, ci.DetailXML
from Candidate_Instance ci 
where ci.Candidate_Instance_Id=2
group by 
ci.Candidate_Id, ci.FirstName, ci.DetailXML

One of the tables have a column which is of XML data type. When I add the column in the select list, I get this error -

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

and when I add the column in the group by clause, I get this error -

The XML data type cannot be compared or sorted, except when using the IS NULL operator.

I am quite confused as to how to come out of this. I want to get the XML data from the column.

Thanks

Answer 1

You cannot group by XML or TEXT columns, you would first need to convert to varchar(max)

select ci.Candidate_Id, ci.FirstName, convert(xml,convert(varchar(max),ci.DetailXML)) DetailXML
from Candidate_Instance ci 
where ci.Candidate_Instance_Id=2
group by 
ci.Candidate_Id, ci.FirstName, convert(varchar(max),ci.DetailXML)

On the first line, it is converted to varchar(max) to match the GROUP BY clause, and later it is re-cast back to XML.