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

发布于 2024-10-20 20:07:33 字数 546 浏览 3 评论 0原文

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

查询 -

    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

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

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

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

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

我很困惑如何走出这个。我想从列中获取 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

饮惑 2024-10-27 20:07:33

不能按 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)

在第一行，它被转换为 varchar(max) 以匹配 GROUP BY 子句，然后将其重新转换回 XML 。

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.

回复收藏 0 原文

离鸿 2024-10-27 20:07:33

根据您问题中的信息，我不太确定您为什么在这里使用 group by 但无论如何这应该有效，因为看起来您只将其包含在 group by 以便能够选择它。

;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

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

回复收藏 0 原文

春庭雪 2024-10-27 20:07:33

如果表中的任何列具有唯一数据，则可以使用 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

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

回复收藏 0 原文

~没有更多了~