在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屋!