SQL如何获取XML数据列的值并在where子句中使用它

用户名

我有一个表，其XML数据类型列称为Settings。XML数据的结构：

<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
...
<root>

表格中还有一ID列，我想用来对结果进行分组。我想计算每个Setting1设置了多少个配置。注意：该字段不是唯一的，可以重复。trueIDID

这是我到目前为止的内容：

with xmlnamespaces (DEFAULT '...')
select count(Setting1) as counts
  from (select Settings.value('(/root/Setting1)[1]', 'nvarchar(max)') as Setting1
    from MY_TABLE
    group by ID) res
  where Setting1= 'true'

但是我收到以下错误：

选择列表中的“ MY_TABLE.Configuration”列无效，因为它既不包含在聚合函数中也不在GROUP BY子句中。

Shnugo

尽管您已经有了答案，但我想添加一些替代方法：

（为Michal Turczyn提供样机）

declare @tbl table (id int, xmlCol xml);
insert into @tbl values
(1, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>'),
(1, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>'),
(2, '<root>
<Setting1>false</Setting1>
<Setting2>false</Setting2>
</root>'),
(2, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>');

最快的应该是简单的XML.exist()：

SELECT t.id,
       sum(CASE WHEN t.xmlCol.exist('/root/Setting1[text()="true"]')=1 THEN 1 ELSE 0 END) howManyTrue
FROM @tbl t
GROUP BY t.id

您可以使用变量在语句外设置SettingName来增强此功能：

DECLARE @SettingName NVARCHAR(100)=N'Setting1';

SELECT t.id,
       sum(CASE WHEN t.xmlCol.exist('/root/*[local-name()=sql:variable("@SettingName") and text()="true"]')=1 THEN 1 ELSE 0 END) howManyTrue
FROM @tbl t
GROUP BY t.id

-并且，如果一个XML设置可能在多个XML中出现多次，则可以使用XQuery的count()：

SELECT t.id,
       sum(t.xmlCol.value('count(/root/*[local-name()=sql:variable("@SettingName") and text()="true"])','int')) howManyTrue
FROM @tbl t
GROUP BY t.id

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。