SQL中如何根据XML列内的DESCRIPTION值筛选数据行？

问题说明 #

DataLogs表包含XML类型列ADescription，该列的XML结构中，<text>元素的value属性里包含DESCRIPTION字段。需要根据指定的DESCRIPTION值筛选对应的数据行，但原有SQL语句无法生效。

示例XML列值 #

<Activity>
  <text value="ID:HELLO, DESCRIPTION:PROJECT112233 , CREDIT:1.83, INTEREST:3.77, TOTAL:5.60" />
</Activity>

无效的SQL语句 #

DECLARE @Description varchar(100) 

SET @Description='PROJECT112233'

SELECT * FROM DataLogs WHERE 
ActivityDescription.exist('"+ @Description +"') = 1

示例数据构建代码 #

create table DataLogs
(
    id int primary key,
    created_date date,
    ADescription XML
  )

INSERT into DataLogs 
values (1,'2024-04-16','<Activity><text value="ID:HELLO, DESCRIPTION:PROJECT112233 , CREDIT:1.83, INTEREST:3.77, TOTAL:5.60"/></Activity>');
insert into DataLogs values (2,'2024-04-12','<Activity>
  <text value="ID:LO, DESCRIPTION:ECT112233 , CREDIT:0.83, INTEREST:0.77, TOTAL:1.60" />
</Activity>');
insert into DataLogs values (3,'2024-04-10','<Activity>
  <text value="ID:Junk, DESCRIPTION:CT33 , CREDIT:1.00, INTEREST:2.00, TOTAL:3.00" />
</Activity>');
insert into DataLogs values (4,'2024-04-01','<Activity>
  <text value="ID:Jk, DESCRIPTION:MT2313 , CREDIT:2.00, INTEREST:4.00, TOTAL:6.00" />
</Activity>');

错误原因 #

原有SQL存在两个核心问题：

1. 列名错误：表中XML列实际名为ADescription，但语句中写了ActivityDescription
2. exist()方法用法错误：该方法需要传入合法的XPath表达式，而非直接拼接变量字符串，原写法完全不符合XPath语法规范

正确解决方案 #

方法1：使用exist()方法精准匹配 #

利用XPath定位到<text>元素的value属性，结合contains()函数和sql:variable()传递参数，实现精准筛选：

DECLARE @Description varchar(100) 
SET @Description='PROJECT112233'

SELECT * FROM DataLogs 
WHERE ADescription.exist('/Activity/text/@value[contains(., concat("DESCRIPTION:", sql:variable("@Description"), " "))]') = 1

• /Activity/text/@value：XPath定位到目标属性
• concat("DESCRIPTION:", sql:variable("@Description"), " ")：拼接成完整的匹配串（末尾加空格避免匹配到类似PROJECT1122334这类包含目标值的字符串）
• contains()：检查属性值是否包含拼接后的匹配串

方法2：使用value()方法结合LIKE匹配 #

先提取XML属性值，再用LIKE进行模糊匹配：

DECLARE @Description varchar(100) 
SET @Description='PROJECT112233'

SELECT * FROM DataLogs 
WHERE ADescription.value('(/Activity/text/@value)[1]', 'varchar(max)') LIKE '%DESCRIPTION:' + @Description + '%'

• value('(/Activity/text/@value)[1]', 'varchar(max)')：提取第一个<text>元素的value属性值
• LIKE：模糊匹配包含目标DESCRIPTION值的行

内容的提问来源于stack exchange，提问作者kumas srunk