Click here to Skip to main content
14,454,263 members
Rate this:
Please Sign up or sign in to vote.
See more:
Please refer the Following xml:-
<test>
<Address>
    <ID>1</ID>
    <CITY>Mumbai</CITY>
 </Address>
<Address>
    <ID>12/ID>
    <CITY>Puna</CITY>
 </Address>
<Address>
    <ID>13/ID>
    <CITY>puna</CITY>
 </Address>
</test>


I want to update the City =Mumbai in all City node in sql.

What I have tried:

I have tried the following the code in sql but it is updating only one value and i want to replace all the values in sql.
Please help

 Declare @xml xml
declare @C varchar(50)
set @C='Mumbai'
set @xml='<test>
<Address>
    <ID>1</ID>
    <CITY>Mumbai</CITY>
 </Address>
<Address>
    <ID>12/ID>
    <CITY>Puna</CITY>
 </Address>
<Address>
    <ID>13/ID>
    <CITY>puna</CITY>
 </Address>
</test>'

SET  @xml.modify('replace value of (/test/Address/CITY/text())[1] with sql:variable("@C")')


the above xml set statement is updating only one value and i want to update all the city nodes
Posted
Updated 10-Jul-19 1:15am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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>
<Address><ID>1</ID><CITY>London</CITY></Address>
<Address><ID>12</ID><CITY>Puna</CITY></Address>
<Address><ID>13</ID><CITY>puna</CITY></Address>
</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 .modify 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
BEGIN
	SET @xml.modify('replace value of (//test/Address[ID=sql:variable("@COUNT")]/CITY/text())[1] with (sql:variable("@C"))')
    SET @COUNT = @COUNT - 1
END
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)[1]', 'INT'),
       [CITY] = Node.Data.value('(CITY)[1]', '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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100