Q:

XML Find and Delete only if text is present

So with the following in a M$SQL 2014 Enterprise Database:
DECLARE @table TABLE (XmlCol XML)
INSERT INTO @table (XmlCol) VALUES ('
<DEFeatureClassInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.8" xsi:type="typens:DEFeatureClassInfo">
  <GPFieldInfoExs xsi:type="typens:ArrayOfGPFieldInfoEx">
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
      <Name>UNITCODE</Name>
      <AliasName>UNITCODE</AliasName>
      <ModelName>UNITCODE</ModelName>
      <FieldType>esriFieldTypeString</FieldType>
      <IsNullable>true</IsNullable>
      <Required>true</Required>
    </GPFieldInfoEx>
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
      <Name>REGIONCODE</Name>
      <AliasName>REGIONCODE</AliasName>
      <ModelName>REGIONCODE</ModelName>
      <DomainName>DOM_REGIONCODE_NPS2016</DomainName>
      <FieldType>esriFieldTypeString</FieldType>
      <DefaultValueString>SER</DefaultValueString>
      <IsNullable>true</IsNullable>
      <Required>true</Required>
    </GPFieldInfoEx>
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
      <Name>CREATEUSER</Name>
      <AliasName>CREATEUSER</AliasName>
      <ModelName>CREATEUSER</ModelName>
      <FieldType>esriFieldTypeString</FieldType>
      <DefaultValueString>GRSM User</DefaultValueString>
      <IsNullable>true</IsNullable>
      <Required>true</Required>
    </GPFieldInfoEx>
  </GPFieldInfoExs>
 </DEFeatureClassInfo>')

I want to delete an (Element?Node?) based on a criteria. In the sample I pasted, I have 3 elements all with 
<Required>true</Required>

What I want to do, is delete
<Required>true</Required>

ONLY when 
<Name>CREATEUSER</Name>

Exists in the same element (I may be confusing nodes and elements here). 
In other words, this is an XML column that defines a DB schema for a table, I have hundreds of tables where I need to modify the XML schema definition such that anytime CREATEUSER appears, the true is deleted (not altered or replaced). 
So far I'm up to 
SELECT *
FROM   GDB_ITEMS
WHERE  GDB_ITEMS.Definition.exist(N'//Name[text()="CREATEUSER"]') = 1;

which took me 4 hours of googling and shows me at least which tables in my DB have CREATEUSER in the XML schema definition. Just need to touch all of those and remove true. To clarify, there are other instances where I do not want to delete true. In the same example posted above, we see true present in the "UNITCODE" node, and true cannot be deleted/removed. I only want to remove true when it's associated with CREATEUSER. 

A:

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (XmlCol XML)
INSERT INTO @tbl (XmlCol) VALUES 
(N'<DEFeatureClassInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                    xmlns:xs="http://www.w3.org/2001/XMLSchema"
                    xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.8"
                    xsi:type="typens:DEFeatureClassInfo">
    <GPFieldInfoExs xsi:type="typens:ArrayOfGPFieldInfoEx">
        <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
            <Name>UNITCODE</Name>
            <AliasName>UNITCODE</AliasName>
            <ModelName>UNITCODE</ModelName>
            <FieldType>esriFieldTypeString</FieldType>
            <IsNullable>true</IsNullable>
            <Required>true</Required>
        </GPFieldInfoEx>
        <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
            <Name>REGIONCODE</Name>
            <AliasName>REGIONCODE</AliasName>
            <ModelName>REGIONCODE</ModelName>
            <DomainName>DOM_REGIONCODE_NPS2016</DomainName>
            <FieldType>esriFieldTypeString</FieldType>
            <DefaultValueString>SER</DefaultValueString>
            <IsNullable>true</IsNullable>
            <Required>true</Required>
        </GPFieldInfoEx>
        <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
            <Name>CREATEUSER</Name>
            <AliasName>CREATEUSER</AliasName>
            <ModelName>CREATEUSER</ModelName>
            <FieldType>esriFieldTypeString</FieldType>
            <DefaultValueString>GRSM User</DefaultValueString>
            <IsNullable>true</IsNullable>
            <Required>true</Required>
        </GPFieldInfoEx>
    </GPFieldInfoExs>
</DEFeatureClassInfo>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @NewValue VARCHAR(10) = '';

UPDATE @tbl
SET XmlCol.modify('replace value of 
(/DEFeatureClassInfo/GPFieldInfoExs/GPFieldInfoEx[Name="CREATEUSER"]/Required/text())[1] with (sql:variable("@NewValue"))');

-- after
SELECT * FROM @tbl;

