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

I am writing a select query in which I am fetching several columns (by joining 3-4 tables). I use group b开发者_运维技巧y 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

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.

I'm not really sure why you are using group by here based on the information in your question but anyway this whould work as it seems you are only including it in the group by in order to be able to select it.

;with cte as
(
    select ci.Candidate_Id, 
           ci.FirstName, 
           ci.DetailXML, 
           ROW_NUMBER() OVER (PARTITION by ci.Candidate_Id, ci.FirstName ORDER BY (SELECT 0)) AS RN
    from Candidate_Instance ci  
    where ci.Candidate_Instance_Id=2 
)
SELECT Candidate_Id, FirstName, DetailXML
FROM cte
WHERE RN=1

If you have any column(s) with unique data in your table you can use a CTE, what would be a fast solution if there is an index on that column(s):

with cte as 
(
select 
    ci.Candidate_Id, 
    ci.FirstName
from Candidate_Instance ci 
where ci.Candidate_Instance_Id=2
group by 
     ci.Candidate_Id, 
     ci.FirstName
)
select 
    a.*,
    b.DetailXML
from cte a
inner join Candidate_Instance b
on a.Candidate_Id = b.Candidate_Id -- <--this must be unique within Candidate_Instance