时间戳

首页 > 解决方案 > 如何根据 SQL 中 XML 属性名称的值过滤行

sql-server - 如何根据 SQL 中 XML 属性名称的值过滤行

问题描述

我有包含以下内容Web.Log的列的表Properties

<properties>
  ...
  <property key="ActivityId" />
  <property key="UserName">John Doe</property>
</properties>

如何Web.Log仅在包含John DoeuserName 属性的行上过滤表？

现在我用

select * from Web.Log
where cast(Properties as nvarchar(max)) 
like '%<property key="UserName">John Doe</property>%'

这感觉像是解决方法:-)

标签： sql-serverxmltsqlxquery

解决方案

使用本机 XML 方法应该更快.exist()。

首先创建一个模型场景来模拟您的问题：

DECLARE @tbl TABLE(ID INT IDENTITY, SomeText VARCHAR(100),Properties XML);
INSERT INTO @tbl VALUES('John Doe exists'
                       ,'<properties>
                            <property key="ActivityId" />
                            <property key="UserName">John Doe</property>
                         </properties>')
                        ,('John Doe doesn''t exist'
                       ,'<properties>
                            <property key="ActivityId" />
                            <property key="UserName">Someone else</property>
                         </properties>')
                        ,('John Doe exists'
                       ,'<properties>
                            <property key="ActivityId" />
                            <property key="UserName">John Doe</property>
                         </properties>');

--我们可以使用变量进行通用过滤

DECLARE @SearchFor VARCHAR(100)='UserName';
DECLARE @FindThis  VARCHAR(100)='John Doe';

--这是查询：

SELECT *
FROM @tbl t 
WHERE t.Properties.exist('/properties/property[@key=sql:variable("@SearchFor") and text()[1]=sql:variable("@FindThis")]')=1;

简而言之：

  • 我们可以.exist()直接在 WHERE 子句中使用。
  • WHERE 为 TRUE，当给定 XPath 至少有一次命中时
  • =1可以设置为以=0返回负集
  • XPath 使用 XQuery 谓词来搜索<property>具有给定键和值的 a。

推荐阅读