如何在FOR XML PATH与子查询场景下搭配Dynamic Data Masking生成XML

根因说明 #

SQL Server的动态数据掩码（DDM）对返回类型为XML的子查询结果会做整体判定：只要子查询中包含被掩码的字段，整个XML类型的返回值会被直接替换为<masked/>，不会深入XML内部对单个字段做掩码处理，这就是子查询节点被整体替换的核心原因。

可行解决方案 #

方案1：临时表中转（推荐，无需调整原有XML生成逻辑） #

先把所有需要的字段查询到临时表/表变量中，此时DDM已经完成对单个敏感字段的掩码，再从已完成掩码的临时数据生成XML，即可保留完整结构，修改后的存储过程如下：

ALTER PROCEDURE [dbo].TestGetUserRecordXml
AS
BEGIN
    SET NOCOUNT ON;
    -- 先查询所有需要的字段，DDM会在这一步对敏感字段完成掩码
    DECLARE @TempUserData TABLE (
        id INT,
        userId INT,
        identityNumber NVARCHAR(50),
        firstName NVARCHAR(50),
        lastName NVARCHAR(50)
    )
    DECLARE @TempUserRole TABLE (
        userId INT,
        userRole NVARCHAR(50)
    )

    INSERT INTO @TempUserData
    SELECT id, userId, identityNumber, firstName, lastName 
    FROM dbo.TestUser

    INSERT INTO @TempUserRole
    SELECT userId, userRole 
    FROM dbo.TestUserRole

    -- 从已完成掩码的临时数据生成XML，不会触发整体掩码替换
    ;WITH XMLNAMESPACES (DEFAULT 'urn:svsys:export:user')
    SELECT
        u.userId
       ,u.identityNumber
       ,u.firstName
       ,u.lastName
       ,(SELECT
                ur.userRole
            FROM @TempUserRole ur
            WHERE ur.userId = u.id
            FOR XML PATH (''), ROOT ('userRoles'), TYPE, ELEMENTS)
    FROM @TempUserData u
    FOR XML PATH ('user'), ROOT ('users')
END
GO

方案2：移除子查询TYPE参数（改动最小） #

如果不需要对嵌套XML做后续的XML类型操作，可以直接去掉子查询的TYPE参数，让子查询返回字符串类型的XML内容，DDM会先完成字段掩码再拼接XML，自然保留结构：

ALTER PROCEDURE [dbo].TestGetUserRecordXml
AS
    WITH XMLNAMESPACES (DEFAULT 'urn:svsys:export:user')
    SELECT
        u.userId
       ,u.identityNumber
       ,u.firstName
       ,u.lastName
       ,(SELECT
                ur.userRole
            FROM dbo.TestUserRole ur
            WHERE ur.userId = u.id
            FOR XML PATH (''), ROOT ('userRoles'), ELEMENTS) -- 仅移除TYPE参数
    FROM dbo.TestUser u
    FOR XML PATH ('user'), ROOT ('users')
GO

效果验证 #

修改完成后使用掩码用户执行存储过程即可得到预期结果：

EXECUTE AS USER = 'UserForMaskedData';
EXEC dbo.TestGetUserRecordXml;
REVERT;

此时返回的XML会保留完整的userRoles、role标签，且role字段内容按掩码规则正常替换。

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