Click here to Skip to main content
15,437,332 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 0:15am

1 solution

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
SQL
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
SQL
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
SQL
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
SQL
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
SQL
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
SQL
UPDATE @temp SET CITY = @C
3) Convert the table back into XML
SQL
SELECT ID, CITY
FROM @temp as [Address]
FOR XML auto,ROOT('test'),ELEMENTS
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900