Inspired by this excellent article Examples of using XQuery to update XML Data in SQL Server, I wrote a quick memo how to update XML data in SQL Server.
1. modify command can update only the first occurrence it finds. The command looks as following.
1 |
SET @x.modify('replace value of (/clients/client/Name/text())[1] with ("***")'); |
2. If you need to update all occurrences, make a loop to iterate all of them.
1 2 3 4 5 6 7 8 9 |
DECLARE @node int; SELECT @node = MAX(@x.value('count(/clients/client/Name)', 'int')) WHILE @node > 0 BEGIN SELECT @x = @x WHERE @x.exist('(/clients/client/Name)[sql:variable("@node")]') = 1; SET @x.modify('replace value of ((/clients/client/Name)[sql:variable("@node")]/text())[1] with ("***")'); SET @node = @node - 1; END |
3. You can substitute a hard-coded path with a variable, but only for the last tag.
1 2 |
DECLARE @path nvarchar(100) = 'Name'; SET @x.modify('replace value of ((/clients/client/*[local-name() = sql:variable("@path")]/text())[1]) with ("***")'); |
Source code for this example update_xml.zip