SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE xmltable (xmlcolumn xml);
INSERT xmltable 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>
  <foo>
    <property name="Name">Foo 3</property>
    <property name="Gender">F</property>
    <property name="DOB">2020-01-03</property>
  </foo>
</array>
')
1 rows affected

UPDATE xmltable
SET xmlcolumn = xmlcolumn.query('
  <array>
    {
    	for $foo in array/foo
    	return
    	  <foo>
    	    {
    	      let $prop := ($foo/property[@name="Name"])[1]
    	      return
    	      <property name="{$prop/@name}">
    	      { substring(($prop/text())[1], 5) }
    	    </property>
    	    }
    	    {
    	      $foo/property[@name != "Name"]
    	    }
    	  </foo>
    }
  </array>
')
1 rows affected

SELECT * FROM xmltable


xmlcolumn


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