如何在SQL Server中过滤xml数据类型列查询包含指定ID的记录

你之前使用cast(IncomingXML as nvarchar(max))匹配失败的核心原因是LIKE后的字符串没有加N前缀，非Unicode字符串和XML转码后的Unicode内容匹配时会出现编码兼容问题，导致匹配不到结果。以下是两种可选实现方案：

方案1：精准节点查询（推荐，性能更高） #

针对你提供的XML结构，目标ID存放在sws:ADAccount节点下，直接用XML原生的exist()方法查询，支持后续添加XML索引优化，非常适合大容量表场景：

-- 定义你要查询的目标ID，也可以直接写死在查询语句中
DECLARE @TargetID NVARCHAR(50) = N'SU123456789'

WITH XMLNAMESPACES (
    'http://schemas.xmlsoap.org/soap/envelope/' AS soapenv,
    'abc' AS sws
)
SELECT * 
FROM 你的表名
WHERE IncomingXML.exist('/soapenv:Envelope/soapenv:Body/sws:ProvisionStudent/sws:provisionStudentInput/sws:ADAccount[text()=sql:variable("@TargetID")]') = 1

方案2：全XML模糊匹配（适合不知道ID所在节点的场景） #

如果不确定目标ID存放的节点位置，可以将整个XML内容转为字符串后模糊匹配，注意字符串前加N标识Unicode即可：

SELECT * 
FROM 你的表名
WHERE IncomingXML.value('.', 'nvarchar(max)') LIKE N'%SU123456789%'

如果频繁使用这类模糊查询，建议新增持久化计算列存储转码后的XML字符串，并对计算列建立索引，避免每次查询都做类型转换损耗性能。

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