在SQL Server 2008中使用group选择查询中的xml类型列

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.

我正在编写一个select查询，其中我将获取多个列（通过连接3-4个表）。我使用group by子句对结果进行分组。

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 -

其中一个表有一个XML数据类型的列。当我在选择列表中添加列时，我收到此错误 -

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

列'table.myXML'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

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

当我在group by子句中添加列时，我收到此错误 -

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

除非使用IS NULL运算符，否则无法比较或排序XML数据类型。

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

我很困惑如何摆脱这个。我想从列中获取XML数据。

Thanks

谢谢

3 个解决方案

#1

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

您不能按XML或TEXT列进行分组，首先需要转换为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.

在第一行，它被转换为varchar（max）以匹配GROUP BY子句，稍后它将重新转换回XML。

#2

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

#3

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):

如果您的表中有任何具有唯一数据的列，您可以使用CTE，如果该列上有索引，那么这将是一个快速解决方案：

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

#1