sql-server xml schema enumeration xml.modify

How to set a multi-valued XML attribute using SQLXML modify

I'm trying to use the SQLXML modify function to update a multi-value (xs:list) attribute. I can set multiple values when constructing the XML (from a string), but SQLXML modify doesn't let me set multiple values.

The initial XML:

<AccessControlList xmlns="http://www.acme.com/Authorization/2013/01">
  <AccessControlRecord Permissions="Fullcontrol" />
  <AccessControlRecord Permissions="DenyCreate DenyRead DenyUpdate DenyDelete" />
</AccessControlList>

Setting a SINGLE value works fine:

DECLARE @SingleValue NVARCHAR(100) = 'DenyCreate';
WITH XMLNAMESPACES ( 'http://www.acme.com/Authorization/2013/01' AS A )
UPDATE dbo.Widget
SET ACL.modify('replace value of (/A:AccessControlList/A:AccessControlRecord/@Permissions)[1] with sql:variable("@SingleValue") cast as A:AccessPermissions ?')
FROM dbo.Widget;

Setting MULTIPLE values FAILS:

DECLARE @MultipleValues NVARCHAR(100) = 'DenyCreate DenyRead DenyUpdate DenyDelete';
WITH XMLNAMESPACES ( 'http://www.acme.com/Authorization/2013/01' AS A )
UPDATE dbo.Widget
SET ACL.modify('replace value of (/A:AccessControlList/A:AccessControlRecord/@Permissions)[1] with sql:variable("@MultipleValues") cast as A:AccessPermissions ?')
FROM dbo.Widget;

with this error:

XQuery: Replacing the value of a node with an empty sequence is allowed only if '()' is used as the new value expression. The new value expression evaluated to an empty sequence but it is not '()'.

The variable is not null or empty. I also tried other variations, which failed with different errors.

Full SQL to reproduce:

-- Drop table and schema collection
IF OBJECT_ID('dbo.Widget') IS NOT NULL
    DROP TABLE dbo.Widget;
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE SCHEMA_NAME(schema_id) = 'dbo' AND name = 'AccessControlList' )
    DROP XML SCHEMA COLLECTION dbo.AccessControlList;
GO

-- Create schema collection
CREATE XML SCHEMA COLLECTION dbo.AccessControlList AS N'
<xs:schema id="AccessControlList" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.acme.com/Authorization/2013/01" xmlns="http://www.acme.com/Authorization/2013/01">
    <xs:simpleType name="AccessPermissions">
        <xs:list>
            <xs:simpleType>
                <xs:restriction base="xs:string">
                    <xs:enumeration value="Create" />
                    <xs:enumeration value="Read" />
                    <xs:enumeration value="Update" />
                    <xs:enumeration value="Delete" />
                    <xs:enumeration value="Execute" />
                    <xs:enumeration value="Fullcontrol" />
                    <xs:enumeration value="DenyCreate" />
                    <xs:enumeration value="DenyRead" />
                    <xs:enumeration value="DenyUpdate" />
                    <xs:enumeration value="DenyDelete" />
                    <xs:enumeration value="DenyExecute" />
                    <xs:enumeration value="FullDeny" />
                </xs:restriction>
            </xs:simpleType>
        </xs:list>
    </xs:simpleType>
    <xs:complexType name="AccessControlRecord">
        <xs:attribute name="Permissions" type="AccessPermissions" use="required" />
    </xs:complexType>
    <xs:complexType name="AccessControlList">
        <xs:sequence>
            <xs:element minOccurs="0" maxOccurs="unbounded" name="AccessControlRecord" type="AccessControlRecord" />
        </xs:sequence>
    </xs:complexType>
    <xs:element name="AccessControlList" nillable="true" type="AccessControlList" />
</xs:schema>
';
GO

-- Create table, insert test data, and display initial state of data
CREATE TABLE dbo.Widget
(
    WidgetId INT PRIMARY KEY IDENTITY(1,1),
    ACL XML(DOCUMENT dbo.AccessControlList)
);
INSERT INTO dbo.Widget
    ( ACL )
VALUES
    ( N'<AccessControlList xmlns="http://www.acme.com/Authorization/2013/01" >
            <AccessControlRecord Permissions="Fullcontrol" />
            <AccessControlRecord Permissions="DenyCreate DenyRead DenyUpdate DenyDelete" />
        </AccessControlList>' );
WITH XMLNAMESPACES ( 'http://www.acme.com/Authorization/2013/01' AS A )
SELECT *
    ,Acr1Permissions = CAST(ACL AS XML).value('(/A:AccessControlList/A:AccessControlRecord)[1]/@Permissions', 'NVARCHAR(128)')
    ,Acr2Permissions = CAST(ACL AS XML).value('(/A:AccessControlList/A:AccessControlRecord)[2]/@Permissions', 'NVARCHAR(128)')
FROM dbo.Widget;

-- Setting a SINGLE value works fine
DECLARE @SingleValue NVARCHAR(100) = 'DenyCreate';
WITH XMLNAMESPACES ( 'http://www.acme.com/Authorization/2013/01' AS A )
UPDATE dbo.Widget
SET ACL.modify('replace value of (/A:AccessControlList/A:AccessControlRecord/@Permissions)[1] with sql:variable("@SingleValue") cast as A:AccessPermissions ?')
FROM dbo.Widget;

-- Display values after
WITH XMLNAMESPACES ( 'http://www.acme.com/Authorization/2013/01' AS A )
SELECT *
    ,Acr1Permissions = CAST(ACL AS XML).value('(/A:AccessControlList/A:AccessControlRecord)[1]/@Permissions', 'NVARCHAR(128)')
    ,Acr2Permissions = CAST(ACL AS XML).value('(/A:AccessControlList/A:AccessControlRecord)[2]/@Permissions', 'NVARCHAR(128)')
FROM dbo.Widget;

/* Setting MULTIPLE values *FAILS*
DECLARE @MultipleValues NVARCHAR(100) = 'DenyCreate DenyRead DenyUpdate DenyDelete';
WITH XMLNAMESPACES ( 'http://www.acme.com/Authorization/2013/01' AS A )
UPDATE dbo.Widget
SET ACL.modify('replace value of (/A:AccessControlList/A:AccessControlRecord/@Permissions)[1] with sql:variable("@MultipleValues") cast as A:AccessPermissions ?')
FROM dbo.Widget;
*/

I get the same failure when I try to set multiple values using "sql:column".

I found this resource (https://learn.microsoft.com/en-us/sql/xquery/type-casting-rules-in-xquery?view=sql-server-2017) that says casting to or from list types is not allowed; I hope there's a solution or workaround.

Is this possible using SQLXML? How?

Thanks in advance

Solution

I must admit, I'd never had to deal with this before...

And I must admit, that I did not find an easy solution. If you find it, please let me know.

Even working with literals leads to the same problem: The string is casted to the enumeration in whole, which does not match one of the allowed values, hence coming back as empty.

But you can do

replace value of (/A:AccessControlList/A:AccessControlRecord/@Permissions)[1] 
with for $p in ("DenyCreate","DenyUpdate","DenyDelete") 
     return $p cast as A:AccessPermissions ?

This will use a XQuery for to run through a list and return each value one-by-one, each casted to the needed type separately.

But the only way I found to use this with an external variable is dynamic SQL. So this works, but is rather ugly:

DECLARE @MultipleValues VARCHAR(100)='DenyCreate DenyUpdate DenyDelete';

DECLARE @cmd NVARCHAR(MAX)=
'WITH XMLNAMESPACES ( ''http://www.acme.com/Authorization/2013/01'' AS A )
 UPDATE dbo.Widget
 SET ACL.modify(''replace value of (/A:AccessControlList/A:AccessControlRecord/@Permissions)[1] with for $p in ("' + REPLACE(@MultipleValues,' ','","') + '") return $p cast as A:AccessPermissions ?'')
 FROM dbo.Widget;';

EXEC(@cmd);