在SQL Server 2008组中选择查询中选择一个xml类型列 [英] select an xml type column in select query with group by SQL Server 2008

问题描述

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

查询 -

 从Candidate_Instance ci 
中选择ci.Candidate_Id，ci.FirstName，ci.DetailXML 
其中ci.Candidate_Instance_Id = 2 
组合
 ci.Candidate_Id，ci。 FirstName，ci.DetailXML 
  

其中一个表具有XML数据类型的列。当我在列表中添加列时，出现此错误 -

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

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

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

<对于如何摆脱这个问题，我感到很困惑。
我想从列中获取XML数据。

谢谢

解决方案

你不能通过XML或TEXT列进行分组，你首先需要转换为varchar（max）

 选择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）
  

在第一行，它被转换为varchar（max）以匹配GROUP BY子句，随后它被重新转换回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.

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.

这篇关于在SQL Server 2008组中选择查询中选择一个xml类型列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！