Andrew Fraser DBA

Microsoft Sql Server search and replace data inside xml

If column is of XML type or you can change to XML type, update can be done in a single statement:

ALTER TABLE myTable ALTER COLUMN col2 XML ;
GO
UPDATE myTable SET col2.modify ( 'replace value of (/loadTest/Groups/Group[Name/text()[1]=("testScript1")]/VUsersNumber/text())[1] with ("2300")' )
WHERE col1 = 358 ;
GO

Otherwise use cast:

DECLARE @l_xml XML
SELECT @l_xml = CAST ( col2 AS XML ) FROM myTable WHERE col1 = 358
SET @l_xml.modify ( 'replace value of (/loadTest/Groups/Group[Name/text()[1]=("testScript1")]/VUsersNumber/text())[1] with ("2300")' )
UPDATE myTable SET col2 = CAST ( @l_xml AS VARCHAR ( MAX ) ) WHERE col1 = 358
GO

Test data used:

CREATE TABLE myTable ( col1 INT , col2 VARCHAR ( MAX ) ) ;
GO
INSERT myTable ( col1 , col2 ) VALUES ( 358 ,
'

  true
  true
  2
  
    
      true
      1
      testScript1
      1
      true
      testScript1
      3
    
    
      true
      2
      testScript2
      2
      true
      testScript2
      57
    
    
      true
      3
      testScript3
      3
      true
      testScript3
      23
    
  
'
) ;
GO

h/t Seth Delconte (more examples at that link) and Mikael Eriksson.