Updating GDB_Items XML Definitions

First, run a query to identify the feature class "PhysicalName" or UID from GDB_ITEMS.
Note: The physical name appears as DBNAME.SCHEMA.FCNAME. Use one of the two for the WHERE clause in the other queries.

First Query

USE [DBName]
SELECT
 ITEMS.ObjectID
 ,ITEMS.UUID
 ,ITEMTYPES.Name as ItemType
 ,ITEMS.[PhysicalName]
 ,ITEMS.[Definition]
  FROM [sde].[GDB_ITEMS] as ITEMS
 INNER JOIN [sde].[GDB_ITEMTYPES] ITEMTYPES
 ON [ITEMS].[Type]=[ITEMTYPES].[UUID]
  WHERE [PhysicalName] like '%FC Name%'

In the results, click the XML link under the Definition field. You should see something similar to the XML below. Notice the highlighted line of code. This is the "Remarks" field. The node "IsNullable" is set as "false". I want the Remarks field to allow NULL values and would like to update it from "false" to "true".
Note: The base and delta tables should be updated as well.

XML Definition View

<DEFeatureClassInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:...>
  <CatalogPath>\DBName.Schema.FCName\DBName.Schema.FCName</CatalogPath>
  <Name>DBName.Schema.FCName</Name>
  <ChildrenExpanded>false</ChildrenExpanded>
  <DatasetType>esriDTFeatureClass</DatasetType>
  <DSID>502</DSID>
  <Versioned>true</Versioned>
  <CanVersion>true</CanVersion>
  <ConfigurationKeyword />
  <RequiredGeodatabaseClientVersion>10.1</RequiredGeodatabaseClientVersion>
  <HasOID>true</HasOID>
  <OIDFieldName>OBJECTID</OIDFieldName>
  <GPFieldInfoExs xsi:type="typens:ArrayOfGPFieldInfoEx">
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
Field 1<Name>OBJECTID</Name>
      <ModelName>OBJECTID</ModelName>
      <FieldType>esriFieldTypeOID</FieldType>
      <IsNullable>false</IsNullable>
      <Required>true</Required>
      <Editable>false</Editable>
    </GPFieldInfoEx>
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
Field 2<Name>Shape</Name>
      <ModelName>Shape</ModelName>
      <FieldType>esriFieldTypeGeometry</FieldType>
      <IsNullable>true</IsNullable>
      <Required>true</Required>
    </GPFieldInfoEx>
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
Field 3<Name>Remarks</Name>
      <AliasName>Remarks</AliasName>
      <ModelName>Remarks</ModelName>
      <FieldType>esriFieldTypeString</FieldType>
      <IsNullable>false</IsNullable>
    </GPFieldInfoEx>

The "Remarks" field is the third field listed. Before I make the update, I want to run an XQuery to list the current "IsNullable" value for third field [3], or "Remarks".

Second Query

SELECT [Definition].value(
 '(/DEFeatureClassInfo/GPFieldInfoExs/GPFieldInfoEx/IsNullable)[3]'
 ,'nvarchar(10)')
 as IsNullable
FROM [<Database Name>].[sde].[GDB_ITEMS]
where PhysicalName = 'DatabaseName.Schema.FCName'
go

The result lists "false" which is correct. Now I will execute an update and use XQuery modify syntax. I am replacing the text value of field number [3] ("Remarks") with "true".

Third Query

UPDATE [<Database Name>].[sde].[GDB_ITEMS]
SET [Definition].modify('
 replace value of 
 (/DEFeatureClassInfo/GPFieldInfoExs/GPFieldInfoEx/IsNullable/text())[3]
 with ("true")')
where PhysicalName = 'DatabaseName.Schema.FCName'
go

I like to execute the first query, again, and ensure the change has been made by viewing the definition XML.

XML Definition View

<DEFeatureClassInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:...>
  <CatalogPath>\DBName.Schema.FCName\DBName.Schema.FCName</CatalogPath>
  <Name>DBName.Schema.FCName</Name>
  <ChildrenExpanded>false</ChildrenExpanded>
  <DatasetType>esriDTFeatureClass</DatasetType>
  <DSID>502</DSID>
  <Versioned>true</Versioned>
  <CanVersion>true</CanVersion>
  <ConfigurationKeyword />
  <RequiredGeodatabaseClientVersion>10.1</RequiredGeodatabaseClientVersion>
  <HasOID>true</HasOID>
  <OIDFieldName>OBJECTID</OIDFieldName>
  <GPFieldInfoExs xsi:type="typens:ArrayOfGPFieldInfoEx">
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
      <Name>OBJECTID</Name>
      <ModelName>OBJECTID</ModelName>
      <FieldType>esriFieldTypeOID</FieldType>
      <IsNullable>false</IsNullable>
      <Required>true</Required>
      <Editable>false</Editable>
    </GPFieldInfoEx>
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
      <Name>Shape</Name>
      <ModelName>Shape</ModelName>
      <FieldType>esriFieldTypeGeometry</FieldType>
      <IsNullable>true</IsNullable>
      <Required>true</Required>
    </GPFieldInfoEx>
    <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
      <Name>Remarks</Name>
      <AliasName>Remarks</AliasName>
      <ModelName>Remarks</ModelName>
      <FieldType>esriFieldTypeString</FieldType>
      <IsNullable>true</IsNullable>
    </GPFieldInfoEx>

The "Remarks" field will now accept NULL values.
This was a valuable first step to understanding XML and XQuery.