Here is another approach
DECLARE @xml xml
DECLARE @li varchar(max) ='Segment Detail'
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>
</PWR_ViewAll>')
SELECT @xml=xmldata from #tmpData where ID=1
declare
anonymous userGroupKeyvalue varchar(100),
@newGroupKeyValue varchar(100);
SELECT anonymous userGroupKeyvalue = col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)')
FROM @xml.nodes('/PWR_ViewAll/dgvViewAll_Vertical') AS tab (col)
WHERE CHARINDEX(@li, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0;
--@newGroupKeyValue = anonymous usergroupkeyvalue but stuff/"replace" string between 2nd&3rd ~ with @newXfundCode
select @newGroupKeyValue =
--"replace/stuff" the old xfundcode := all chars from position of 2nd ~ (+1) till position of the 2nd ~ (minus 1) with an empty string
stuff(anonymous userGroupKeyvalue,
--starting from position of 2nd ~ +1
charindex('~', anonymous userGroupKeyvalue, charindex('~', anonymous userGroupKeyvalue, 1)+1)+1,
--as many characters as exist between 3rd ~ and 2nd ~ (excluding ~)
charindex('~', anonymous userGroupKeyvalue, charindex('~', anonymous userGroupKeyvalue, charindex('~', anonymous userGroupKeyvalue, 1)+1)+1)-1
-
charindex('~', anonymous userGroupKeyvalue, charindex('~', anonymous userGroupKeyvalue, 1)+1),
--stuff the new xfundcode
@newXfundCode
);
--test
select anonymous userGroupKeyValue as oldgroupkeyvalue, @newGroupKeyValue as newgroupkeyvalue;
--test
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("anonymous userGroupKeyValue")]/text())[1]
with sql:variable("@newGroupKeyValue")
'
);
select @xml as newxml;
--update #tmpData set xmldata = @xml where ID=1;
--select * from #tmpData;
Drop table #tmpData
Here is 2nd approach
DECLARE @xml xml
DECLARE @li varchar(max) ='Consensus Model'
DECLARE anonymous userXfundCode varchar(max) ='TRIN'
DECLARE @newXfundCode varchar(max) ='TEST105'
DECLARE @NewGroupKey NVARCHAR(max)
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>
</PWR_ViewAll>')
SELECT @xml=xmldata from #tmpData where ID=1
DECLARE @r INT =1
DECLARE @Cntr INT = 0
CREATE TABLE #TempData1
(
ID INT Identity,
GroupKey NVARCHAR(MAX)
)
DECLARE
anonymous userGroupKeyvalue nvarchar(100),
@newGroupKeyValue nvarchar(100),
@GrpKey nvarchar(MAX);
INSERT INTO #TempData1
SELECT col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)')
FROM @xml.nodes('/PWR_ViewAll/dgvViewAll_Vertical') AS tab (col)
WHERE CHARINDEX(@li, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0
SELECT @Cntr = MAX(ID) FROM #TempData1
While @r<=@Cntr
BEGIN
SELECT @GrpKey=GroupKey FROM #TempData1 WHERE ID=@r
IF CHARINDEX(@li,@GrpKey) > 0 AND CHARINDEX(anonymous userXfundCode,@GrpKey) > 0
BEGIN
SET anonymous userGroupKeyValue=@GrpKey
SELECT * INTO #tmpSingleRec FROM
(SELECT ID,[DATA] FROM SplitStringToTable(@GrpKey,'~')) a
IF EXISTS(SELECT count(*) FROM #tmpSingleRec)
BEGIN
UPDATE #tmpSingleRec SET DATA=@newXfundCode WHERE ID=3
SELECT @NewGroupKey=COALESCE(@NewGroupKey + '~', '')+[DATA] FROM #tmpSingleRec
set @xml.modify('
replace value of (/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[.=sql:variable("anonymous userGroupKeyValue")]/text())[1]
with sql:variable("@NewGroupKey")
'
);
END
END
SET @r=@r+1
END
UPDATE #tmpData SET xmldata=@xml WHERE ID=1
SELECT * FROM #tmpData
IF OBJECT_ID('TempDB..#TempData1') IS NOT NULL
BEGIN
drop table #TempData1
END
IF OBJECT_ID('TempDB..#tmpData') IS NOT NULL
BEGIN
drop table #tmpData
END
IF OBJECT_ID('TempDB..#tmpSingleRec') IS NOT NULL
BEGIN
drop table #tmpSingleRec
END
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(80, 33%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETZ%3C/text%3E%3C/svg%3E)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)