First off, there are typing mistakes in your xml - it is not well formed - see ID 12 and ID 13 closing tags. It should be
declare @C varchar(50) = 'Mumbai'
declare @xml xml = N'<test>
Secondly, it is hard to tell that you updating only one value as 'Mumbai' was the value of the first instance of CITY anyway. I've changed that to 'London' in my test code.
Now to the solution. You can only change one node at a time using
so you will need to use a loop. Normally I would not advocate using a loop for anything in SQL but this is not a table per se.
To get the number of times you need to execute the loop you need to examine the ID nodes. It is not as simple as just counting them - when I tried to use
SET @xml.modify('replace value of (//test/Address/CITY/text())[sql:variable("@COUNT")] with (sql:variable("@C"))')
I got persistent errors along the lines of "The target of 'replace' must be at most one node" so I need to find the highest value of ID which I can do with
DECLARE @COUNT INT
SET @COUNT = @XML.value ('max(/test/Address/ID)', 'int');
I then need to put the update into a loop with a little xquery select like this
WHILE @COUNT > 0
SET @xml.modify('replace value of (//test/Address[ID=sql:variable("@COUNT")]/CITY/text()) with (sql:variable("@C"))')
SET @COUNT = @COUNT - 1
Note, because the ID values are not contiguous the loop will not do anything on most of the iterations i.e. when @COUNT = 11,10,9,8,7,6,5,4,3 and 2
I said I didn't like using loops in SQL and I really meant it, so here is alternative, and, for me, far more elegant approach.
1) Create a table from the XML
DECLARE @temp table (ID int, CITY varchar(max))
INSERT INTO @temp
SELECT [ID] = Node.Data.value('(ID)', 'INT'),
[CITY] = Node.Data.value('(CITY)', 'VARCHAR(MAX)')
FROM @XML.nodes('/test/Address') Node(Data)
2) Update the table as a set
UPDATE @temp SET CITY = @C
3) Convert the table back into XML
SELECT ID, CITY
FROM @temp as [Address]
FOR XML auto,ROOT('test'),ELEMENTS