SQL Server All data is not updating in xml

Question

see the code first

DECLARE @xml xml
 DECLARE @oldli varchar(max) ='Total Revenue'
 DECLARE @newli varchar(max) ='Gross Revenue'
 --DECLARE @newXfundCode varchar(max) ='TEST'

 CREATE TABLE #tmpData (id INT, xmldata xml)  

INSERT INTO #tmpData (id, xmldata) 
VALUES (1,N'<?xml version="1.0" encoding="utf-16"?>
<PWR_ViewAll>
<dgvViewAll_Vertical>
<Section_x0020_>ZB-P1</Section_x0020_>
<LineItem>B. Riley FBR Inc.</LineItem>
<Revise_x0020_Date>08-21-2020</Revise_x0020_Date>
<GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey>
</dgvViewAll_Vertical>
<dgvViewAll_Vertical>
<Section_x0020_>CL</Section_x0020_>
<LineItem>Deutsche Bank</LineItem>
<Revise_x0020_Date>02-28-2020</Revise_x0020_Date>
<GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>
</dgvViewAll_Vertical>
<dgvViewAll_Vertical>
<Section_x0020_>CL</Section_x0020_>
<LineItem>Deutsche Bank</LineItem>
<Revise_x0020_Date>02-28-2020</Revise_x0020_Date>
<GroupKey>Segment Detail~Net Income~RD_100~NBM~~1~CL</GroupKey>
</dgvViewAll_Vertical>
</PWR_ViewAll>')

 SELECT @xml=xmldata from #tmpData where ID=1

 declare 
  @userGroupKeyvalue varchar(100), 
  @newGroupKeyValue varchar(100);

 SELECT @userGroupKeyvalue = col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)')
 FROM @xml.nodes('/PWR_ViewAll/dgvViewAll_Vertical') AS tab (col)
 WHERE  CHARINDEX(@oldli, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0;

 select  @newGroupKeyValue = stuff(@userGroupKeyvalue, charindex('~', @userGroupKeyvalue, 1)+1,Len(@oldli),@newli)

 select @userGroupKeyValue as oldgroupkeyvalue, @newGroupKeyValue as newgroupkeyvalue;


 select @xml as oldxml;

 --modify xml, replace the text of GroupKey element whose text equals anonymous userGroupKeyValue with @newGroupKeyValue
 --if newGroupKeyValue is not null etc...
 set @xml.modify('
   replace value of (/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[.=sql:variable("@userGroupKeyValue")]/text())[1]  
   with sql:variable("@newGroupKeyValue") 
 '
 );

 select @xml as newxml;

 Drop table #tmpData

at the top there is two variable called

     DECLARE @oldli varchar(max) ='Total Revenue'
     DECLARE @newli varchar(max) ='Gross Revenue'

i have to replace all text ~Total Revenue~ with ~Gross Revenue~ which is stored GroupKey tag.

if you run my above code then must see that all Total Revenue not change to Gross Revenue. i have see one last one Total Revenue change to Gross Revenue

please guide me what to add in above code as a result all Total Revenue must change to Gross Revenue stored GroupKey tag.
thanks

New code is working

---

DECLARE @Ticker_ViewAll VARCHAR(MAX)='/PWR_ViewAll'
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);
 INSERT INTO @tbl (xmldata) VALUES
 (N'<PWR_ViewAll>
     <dgvViewAll_Vertical>
         <Section_x0020_>ZB-P1</Section_x0020_>
         <LineItem>B. Riley FBR Inc.</LineItem>
         <Revise_x0020_Date>08-21-2020</Revise_x0020_Date>
         <GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey>
     </dgvViewAll_Vertical>
     <dgvViewAll_Vertical>
         <Section_x0020_>CL</Section_x0020_>
         <LineItem>Deutsche Bank</LineItem>
         <Revise_x0020_Date>02-28-2020</Revise_x0020_Date>
         <GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>
     </dgvViewAll_Vertical>
     <dgvViewAll_Vertical>
         <Section_x0020_>CL</Section_x0020_>
         <LineItem>Deutsche Bank</LineItem>
         <Revise_x0020_Date>02-28-2020</Revise_x0020_Date>
         <GroupKey>Segment Detail~Net Income~RD_100~NBM~~1~CL</GroupKey>
     </dgvViewAll_Vertical>
 </PWR_ViewAll>');
 -- DDL and sample data population, end

 DECLARE @from VARCHAR(30) = '~Total Revenue~'
    , @to VARCHAR(30) = '~Gross Revenue~';

 -- before
 SELECT * FROM @tbl
 WHERE xmldata.exist('/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]') = 1;

 DECLARE @UPDATE_STATUS BIT = 1;

 WHILE @UPDATE_STATUS > 0
 BEGIN
    UPDATE t
    SET xmldata.modify('replace value of (/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]/text())[1]
       with (sql:column("t1.c"))')
    FROM @tbl AS t
       CROSS APPLY (SELECT REPLACE(xmldata.value('(/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]/text())[1]', 'VARCHAR(100)'),@from,@to)) AS t1(c)
    WHERE xmldata.exist('/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]') = 1;

    SET @UPDATE_STATUS = @@ROWCOUNT;
    PRINT @UPDATE_STATUS;
 END;

 -- after
 SELECT * FROM @tbl;

Answer 1

Hi @T.Zacks ,

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);  
INSERT INTO @tbl (xmldata) VALUES  
(N'<PWR_ViewAll>  
	<dgvViewAll_Vertical>  
		<Section_x0020_>ZB-P1</Section_x0020_>  
		<LineItem>B. Riley FBR Inc.</LineItem>  
		<Revise_x0020_Date>08-21-2020</Revise_x0020_Date>  
		<GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey>  
	</dgvViewAll_Vertical>  
	<dgvViewAll_Vertical>  
		<Section_x0020_>CL</Section_x0020_>  
		<LineItem>Deutsche Bank</LineItem>  
		<Revise_x0020_Date>02-28-2020</Revise_x0020_Date>  
		<GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>  
	</dgvViewAll_Vertical>  
	<dgvViewAll_Vertical>  
		<Section_x0020_>CL</Section_x0020_>  
		<LineItem>Deutsche Bank</LineItem>  
		<Revise_x0020_Date>02-28-2020</Revise_x0020_Date>  
		<GroupKey>Segment Detail~Net Income~RD_100~NBM~~1~CL</GroupKey>  
	</dgvViewAll_Vertical>  
</PWR_ViewAll>');  
-- DDL and sample data population, end  
  
DECLARE @from VARCHAR(30) = '~Total Revenue~'  
   , @to VARCHAR(30) = '~Gross Revenue~';  
  
-- before  
SELECT * FROM @tbl  
WHERE xmldata.exist('/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]') = 1;  
  
DECLARE @UPDATE_STATUS BIT = 1;  
  
WHILE @UPDATE_STATUS > 0  
BEGIN  
   UPDATE t  
   SET xmldata.modify('replace value of (/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]/text())[1]  
      with (sql:column("t1.c"))')  
   FROM @tbl AS t  
      CROSS APPLY (SELECT REPLACE(xmldata.value('(/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]/text())[1]', 'VARCHAR(100)'),@from,@to)) AS t1(c)  
   WHERE xmldata.exist('/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]') = 1;  
      
   SET @UPDATE_STATUS = @@ROWCOUNT;  
   PRINT @UPDATE_STATUS;  
END;  
  
-- after  
SELECT * FROM @tbl;