SQL query XML - Modify XML column

              

Scenario: Modify attribute of SQL table XML column

Solution:

DECLARE @temp TABLE
      (
         ID INT,
         [data] XML
      );

    INSERT INTO @temp
    SELECT tuc.ID,
           CAST(uc.[data] AS XML)
    FROM   UserConfig tuc
    WHERE  Config = 'validattion'
           AND CAST(tuc.[data] AS XML).value('(//user/validation/validator[@type="UserDataValidator"]/@enabled[contains(.,"true")])[1]', 'nvarchar(100)') = 'true'

    UPDATE @temp
    SET    [Data].modify('replace value of (//user/validation/validator[@type="UserDataValidator"]/@enabled[contains(.,"true")])[1] with "false"')

    UPDATE uc
    SET    uc.[data] = CAST(tuc.[Data] AS VARCHAR(MAX))
    FROM   [Admin].dbo.UserConfig uc
           JOIN @temp tuc
		        uc.ID = tuc.ID
                AND uc.Config = tuc.Config