Get and update XML value with a certain attribute

Question

I have a SQL table with an xml column that has values like this, across multiple rows:

<array>
  <foo>
    <property name="Name">Foo 1</property>
    <property name="Gender">M</property>
    <property name="DOB">2020-01-01</property>
  </foo>
  <foo>
    <property name="Name">Foo 2</property>
    <property name="Gender">M</property>
    <property name="DOB">2020-01-02</property>
  </foo>
  <foo>
    <property name="Name">Foo 3</property>
    <property name="Gender">F</property>
    <property name="DOB">2020-01-03</property>
  </foo>
</array>

I'd like to write a SQL statement that can update all occurrences of the property["Name"] value to 1 (remove the Foo).

I've been trying SQL such as:

select xmlcolumn.value('/array/foo[@name="Name"]/', 'nvarchar(max)')
from xmltable

and a few variations of that XML query, but haven't had luck in figuring it out. The result should be a table with XML values that look like this (note the property with the Name attribute is set to 1 for all occurrences):

<array>
  <foo>
    <property name="Name">1</property>
    <property name="Gender">M</property>
    <property name="DOB">2020-01-01</property>
  </foo>
  <foo>
    <property name="Name">1</property>
    <property name="Gender">M</property>
    <property name="DOB">2020-01-02</property>
  </foo>
  <foo>
    <property name="Name">1</property>
    <property name="Gender">F</property>
    <property name="DOB">2020-01-03</property>
  </foo>
</array>

Answer 1

Unfortunately, MS SQL Server XQuery implementation is not fully conformant with the XQuery standards. That's why XQuery .modify() method makes one update at a time.

So we can update XML in a loop by using the XQuery .exist() method until there is nothing to update.

Please up-vote my suggestion: https://feedback.azure.com/d365community/idea/153a9604-7025-ec11-b6e6-000d3a4f0da0

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<array>
    <foo>
        <property name="Name">Foo 1</property>
        <property name="Gender">M</property>
        <property name="DOB">2020-01-01</property>
    </foo>
    <foo>
        <property name="Name">Foo 2</property>
        <property name="Gender">M</property>
        <property name="DOB">2020-01-02</property>
    </foo>
</array>'),
(N'<array>
    <foo>
        <property name="Name">Foo 3</property>
        <property name="Gender">F</property>
        <property name="DOB">2020-01-03</property>
    </foo>
</array>');
-- DDL and sample data population, end

DECLARE @from VARCHAR(30) = '1'
   , @to VARCHAR(30) = '1'
   , @UPDATE_STATUS BIT = 1;

-- before
SELECT * FROM @tbl
WHERE xmldata.exist('/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]') = 1;

WHILE @UPDATE_STATUS > 0
BEGIN
   UPDATE t
   SET xmldata.modify('replace value of (/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]/text())[1]
      with (sql:variable("@to"))')
   FROM @tbl AS t
   WHERE xmldata.exist('/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]') = 1;
    
   SET @UPDATE_STATUS = @@ROWCOUNT;
   PRINT @UPDATE_STATUS;
END;

-- after
SELECT * FROM @tbl;