How to replace value with another value of a node in XQuery?

Question

I have a table in SQL Server 14.0, which is called Users .There are multiple users and each of the user has a column data called as PermissionData which is in XML format. The following is the XML :

<Policy>
      <Permissions>
        <Item>
          <Key>Hello</Key>
        </Item>
        <Item>
          <Key>Bye</Key>
        </Item>
      </Permissions>
    </Policy>

So I want to replace all the Bye with GoodBye value. Some of the users may have this Bye value or may not have. Some other users can have this Bye value as the first key or the second key or the third key. So due to that I cant just do [2] at every key because the order can be different.

The end-result should be like this:

 <Policy>
  <Permissions>
    <Item>
      <Key>Hello</Key>
    </Item>
    <Item>
      <Key>GoodBye</Key>
    </Item>
  </Permissions>
</Policy>

Can anyone help me to write an XQuery that updates the Key value for all the users in the Users table? The following is my attempt to write the query, but it is not working.

UPDATE Users
SET PermissionData.modify('replace value of (/Policy/Permissions/Item/Key)
 with( if((/Policy/Permissions/Item/Key/text())="Bye")
      then "GoodBye"
      else () 
     )')

While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, May 21 '21 at 13:32

Yitzhak Khabinsky · Accepted Answer · 2021-05-21T14:32:58.657

Please try the following solution.

Notable points:

XML is case sensitive, that's why use of the lower-case() function.
The XQuery .modify() method updates just one single node at a time.
If XML doesn't have the <Key>...</Key> element at all, or it has a different value, the .modify() method will not affect the XML, and will not error out.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, PermissionData  XML);
INSERT INTO @tbl (PermissionData ) VALUES
(N'<Policy>
    <Permissions>
        <Item>
            <Key>Hello</Key>
            <Value>
                <WorkPermissions>
                    <Operations>
                        <Item>Say1</Item>
                        <Item>Say2</Item>
                    </Operations>
                </WorkPermissions>
            </Value>
        </Item>
        <Item>
            <Key>Bye</Key>
            <Value>
                <WorkPermissions>
                    <Operations>
                        <Item>Say1</Item>
                        <Item>Say2</Item>
                    </Operations>
                </WorkPermissions>
            </Value>
        </Item>
    </Permissions>
</Policy>');
-- DDL and sample data population, end

DECLARE @currentValue VARCHAR(30) = 'bye' -- keep it lower case
    , @newValue VARCHAR(30) = 'GoodBye';

UPDATE @tbl
SET PermissionData.modify('  
  replace value of (/Policy/Permissions/Item/Key[lower-case(./text()[1])=sql:variable("@currentValue")]/text())[1]  
  with (sql:variable("@newValue"))  
');

-- test
SELECT * FROM @tbl;

Output XML

<Policy>
  <Permissions>
    <Item>
      <Key>Hello</Key>
      <Value>
        <WorkPermissions>
          <Operations>
            <Item>Say1</Item>
            <Item>Say2</Item>
          </Operations>
        </WorkPermissions>
      </Value>
    </Item>
    <Item>
      <Key>GoodBye</Key>
      <Value>
        <WorkPermissions>
          <Operations>
            <Item>Say1</Item>
            <Item>Say2</Item>
          </Operations>
        </WorkPermissions>
      </Value>
    </Item>
  </Permissions>
</Policy>

The Permissions Data already contains the Xml, I just need to change the current value in XML bye to GoodBye.So why are we adding the XML to the Permission Data? — AKASH SHIRALE, May 21 '21 at 13:55
@AKASHSHIRALE, the answer is a minimal reproducible example too. You copy it to SSMS as-is, run it, and it works. You don't need to insert anything to your real existing DB table. — Yitzhak Khabinsky, May 21 '21 at 13:58
It gives this error.. Mutator 'modify()' on 'PermissionData' cannot be called on a null value.Because some Users dont have Bye in the PermissionData xml — AKASH SHIRALE, May 21 '21 at 14:26
@AKASHSHIRALE, I already mentioned it in the comments above. While asking a question, you need to provide a minimal reproducible example covering your real scenario. — Yitzhak Khabinsky, May 21 '21 at 14:29

How to replace value with another value of a node in XQuery?

1 Answers1