How to use Dynamic Data Masking with FOR XML PATH and subqueries

Question

I'm trying to generate XML from a database and using the Dynamic Data Masking for hiding sensitive information but when I execute my stored procedures that generate the XML as the user who should only see the masked data, the finished XML lacks the output from subqueries and only generates <masked /> instead of the usual tag and contents of that tag. When I run the stored procedures as my regular db user I get the XML-results I need.

I've tried changing the rights for my "masked db user" but I can't manage to resolve the issue.

This is the content of my stored procedure:

    WITH XMLNAMESPACES (DEFAULT 'urn:svsys:export:user')
    SELECT
        u.userId
       ,u.identityNumber
       ,u.firstName
       ,u.lastName
       ,(SELECT
                ur.role
            FROM dbo.UserRole ur
            WHERE ur.userId = u.id
            FOR XML PATH (''), ROOT ('userRoles'), TYPE, ELEMENTS)
    FROM dbo.[User] u
    INNER JOIN dbo.LegalCareUnit lcu
        ON u.legalCareUnitId = lcu.id
    WHERE lcu.legalCareUnitId = @LegalCareUnitId
    FOR XML PATH ('user'), ROOT ('users')

and an example of output when executed by my regular db user, ie. without Dynamic Data Masking:

<users xmlns="urn:svsys:export:user">
  <user>
    <userId>2</userId>
    <identityNumber>111</identityNumber>
    <firstName>Paddy</firstName>
    <lastName>Smith</lastName>
    <userRoles xmlns="urn:svsys:export:user">
      <role>testRole</role>
    </userRoles>
  </user>
</users>

and how the XML looks when executing as my masked db user (with Dynamic Data Masking):

<users xmlns="urn:svsys:export:user">
  <user>
    <userId>2</userId>
    <identityNumber>xxx</identityNumber>
    <firstName>Paxxx</firstName>
    <lastName>Smxxx</lastName>
    <masked xmlns="" />
  </user>
</users>

what I would like to get when executing as my masked db user (with Dynamic Data Masking):

<users xmlns="urn:svsys:export:user">
  <user>
    <userId>2</userId>
    <identityNumber>xxx</identityNumber>
    <firstName>Paxxx</firstName>
    <lastName>Smxxx</lastName>
    <userRoles xmlns="urn:svsys:export:user">
      <role>texxxxxx</role>
    </userRoles>
  </user>
</users>

As you can see the tag in my first example <userRoles xmlns="urn:svsys:export:user"> is replaced by <masked xmlns="" /> in the second example.

Any idea how I can get the tag <userRoles> with masked information inside the child tag <role>, as in my last example?

SETUP for testing

CREATE TABLE dbo.[TestUser]
(
    id INT PRIMARY KEY IDENTITY(1,1),
    userId INT NOT NULL,
    identityNumber NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(4, "0101", 0)')  NOT NULL,
    firstName NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(2, "xxxxxxxxx", 0)') NOT NULL,
    lastName NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(2, "xxxxxxxxx", 0)') NOT NULL
)


CREATE TABLE dbo.TestUserRole
(
    id INT PRIMARY KEY IDENTITY(1,1),
    userId INT NOT NULL,
    userRole NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(2, "xxxxxxxxx", 0)') NOT NULL
)

ALTER TABLE [dbo].[TestUserRole]  WITH CHECK ADD CONSTRAINT [FK_TestUser_UserRole] FOREIGN KEY([userId])
REFERENCES [dbo].[User] ([id])

SET IDENTITY_INSERT TestUser ON

INSERT INTO TestUser (id, userId, identityNumber, firstName, lastName)
    VALUES (1, 200, N'19520102', N'Paddy', N'Smith'),
    (2, 300, N'19500609', N'Trevor', N'Bolder'),
    (3, 400, N'19460526', N'Mick', N'Ronson')

SET IDENTITY_INSERT TestUser OFF

INSERT INTO TestUserRole (userId, userRole)
    VALUES (1, N'Roadie'),
    (2, N'Bassist'),
    (3, N'Guitarist'),
    (3, N'Pianist')
GO

CREATE 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'), TYPE, ELEMENTS)
    FROM dbo.TestUser u
    FOR XML PATH ('user'), ROOT ('users')
GO

CREATE USER [UserForMaskedData] WITHOUT LOGIN;
GRANT EXECUTE ON SCHEMA::dbo TO UserForMaskedData;
GRANT SELECT ON SCHEMA::dbo TO UserForMaskedData;

Answer 1

Got the answer from Erland Sommarskog at Microsoft Docs (https://learn.microsoft.com/en-us/answers/questions/526979/generating-xml-from-sql-with-dynamic-data-masking.html)

In order for the masking to work properly in subqueries one has to put that into a temporary table, so the stored procedure should look like this:

SELECT * INTO #testis FROM TestUserRole;
WITH XMLNAMESPACES (DEFAULT 'urn:svsys:export:user')
SELECT
    u.userId
   ,u.identityNumber
   ,u.firstName
   ,u.lastName
   ,(SELECT
            ur.userRole
        FROM #testis ur
        WHERE ur.userId = u.id
        FOR XML PATH (''), ROOT ('userRoles'), TYPE, ELEMENTS)
FROM dbo.TestUser u
FOR XML PATH ('user'), ROOT ('users')

when executing the stored procedure as the user UserForMaskedData I'm getting the results I'm after:

<users xmlns="urn:svsys:export:user">
  <user>
    <userId>200</userId>
    <identityNumber>19520101</identityNumber>
    <firstName>Paxxxxxxxxx</firstName>
    <lastName>Smxxxxxxxxx</lastName>
    <userRoles xmlns="urn:svsys:export:user">
      <userRole>Roxxxxxxxxx</userRole>
    </userRoles>
  </user>
  <user>
    <userId>300</userId>
    <identityNumber>19500101</identityNumber>
    <firstName>Trxxxxxxxxx</firstName>
    <lastName>Boxxxxxxxxx</lastName>
    <userRoles xmlns="urn:svsys:export:user">
      <userRole>Baxxxxxxxxx</userRole>
    </userRoles>
  </user>
  <user>
    <userId>400</userId>
    <identityNumber>19460101</identityNumber>
    <firstName>Mixxxxxxxxx</firstName>
    <lastName>Roxxxxxxxxx</lastName>
    <userRoles xmlns="urn:svsys:export:user">
      <userRole>Guxxxxxxxxx</userRole>
      <userRole>Pixxxxxxxxx</userRole>
    </userRoles>
  </user>
</users>