SQL Server XQuery：如何判断XML列表是否包含指定多个值？

嘿，这个问题我之前也碰到过，手动拼接exist条件确实麻烦，尤其是角色数量不固定的时候，还容易踩SQL注入的坑。给你分享几个更优雅的方案，都是SQL Server里处理这种动态多角色查询的常用手段：

方案1：使用表值参数（推荐，安全且灵活） #

表值参数是SQL Server里专门用来传递多行数据的类型，完美适配你这种角色数量不固定的场景。步骤很简单：

首先创建一个用户定义的表类型，用来存放要查询的角色：

CREATE TYPE dbo.RoleListType AS TABLE (Role NVARCHAR(100) NOT NULL);

然后不管你要查1个还是N个角色，只需要把角色塞进这个表变量里，再用EXISTS关联查询就行：

-- 示例：准备要查询的角色列表
DECLARE @RolesToFind dbo.RoleListType;
INSERT INTO @RolesToFind (Role) VALUES ('Race'), ('Mountain'), ('City');

-- 执行查询
SELECT bt.Id, bt.XmlData
FROM BikeTable bt
WHERE EXISTS (
    SELECT 1
    FROM @RolesToFind rt
    WHERE bt.XmlData.exist('/Bike/Roles[string = sql:column("rt.Role")]') = 1
);

这个方案的好处是完全不用拼接SQL，既安全（避免注入）又易维护，不管角色数量怎么变，查询逻辑都不用改。如果是写存储过程的话，直接把表值参数作为输入参数就行。

方案2：将XML角色转为关系型表（适合高频查询场景） #

如果你的查询频率很高，而且数据量不小，XML查询的性能可能会有点跟不上。这时候可以把XML里的角色数据同步到一张专门的关系表中，后续查询就变成普通的关系型查询，速度会快很多：

首先新建一张关联表：

CREATE TABLE BikeRoles (
    BikeId UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES BikeTable(Id),
    Role NVARCHAR(100) NOT NULL,
    PRIMARY KEY (BikeId, Role) -- 避免重复角色
);

然后把现有XML里的角色数据导入进去（可以写触发器或者在插入/更新BikeTable时同步）：

INSERT INTO BikeRoles (BikeId, Role)
SELECT 
    bt.Id,
    r.value('.', 'NVARCHAR(100)') AS Role
FROM BikeTable bt
CROSS APPLY bt.XmlData.nodes('/Bike/Roles/string') AS Roles(r)
-- 避免重复插入
WHERE NOT EXISTS (
    SELECT 1 FROM BikeRoles br WHERE br.BikeId = bt.Id AND br.Role = r.value('.', 'NVARCHAR(100)')
);

之后查询就非常简单了，用普通的JOIN或者IN就能搞定：

DECLARE @RolesToFind TABLE (Role NVARCHAR(100));
INSERT INTO @RolesToFind VALUES ('Race'), ('Mountain');

SELECT DISTINCT bt.Id, bt.XmlData
FROM BikeTable bt
JOIN BikeRoles br ON bt.Id = br.BikeId
JOIN @RolesToFind rt ON br.Role = rt.Role;

还可以给BikeRoles的Role字段建索引，查询性能会进一步提升。

方案3：用字符串拆分函数（轻量临时查询） #

如果你的SQL Server版本是2016及以上，也可以直接用STRING_SPLIT函数把逗号分隔的角色字符串拆分成行，再关联查询，适合临时查询或者简单场景：

DECLARE @RoleString NVARCHAR(MAX) = 'Race,Mountain'; -- 逗号分隔的角色列表

SELECT bt.Id, bt.XmlData
FROM BikeTable bt
WHERE EXISTS (
    SELECT 1
    FROM STRING_SPLIT(@RoleString, ',') AS ss
    WHERE bt.XmlData.exist('/Bike/Roles[string = sql:column("ss.value")]') = 1
);

注意：如果你的角色值里包含逗号，记得换一个不会冲突的分隔符（比如|）。

额外注意点 #

如果你的XML带有命名空间，记得在查询前加上WITH XMLNAMESPACES声明，不然exist查询会找不到节点。比如：

WITH XMLNAMESPACES (DEFAULT 'http://your-namespace-url.com')
SELECT ... -- 后面的查询逻辑不变

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