How to find and replace data in xml by xquery

Question

How to find and replace data by xquery in below xml. below xml is stored in a table column whose data type is xml.

i will have a store procedure where i will send line item and xfund code as parameter. i want to search group key by line item & replace xfund code in xml with new xfundcode will be provided to SP.

speciall see this line where lineitem and xfund code is there concatinated by ~ sign.
<GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey>

1. GroupKey field has many data concatinated by ~ sign.
2. line item and xfundcode is 2nd and 3rd element in group key separated by ~sign.
3. i want to search line item in group key and replace old xfundcode by new xfundcode.
4. Lineitem and new xfundcode will be pass to SP as parameter

in group key first element is Section, second element is Line Item and second element is xfundcode. all are separated by ~ sign.

the below xml is stored in table column.
so please share a query by which i can use to find line item in groupkey in the stored xml in table column. if line item found then xfund code in group key will be replaced with a xfund code which will be passed to SP and xml will be updated in table column.

<?xml version="1.0" encoding="utf-8"?>
<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>Consensus Model~Total Revenue~TRIN~NBM~~1~CL</GroupKey>
  </dgvViewAll_Vertical>
</PWR_ViewAll>

Thanks

EDIT

Sir thanks for your link but my scenario is bit different i want to search data in group key

<GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey> has few data in it concatenated by ~ sign.

so i want to search line item in group key field. if match then replace it xfund code by new xfund code

1) first element in GroupKey is section, 2nd one is line item, 3rd one is xfundcode and rest element in group key not known to me.

so i want to search on group key in xml and if line item found then replace xfundcode. see my code here. now tell me how could i replace xfundcode in xml and update that xml in table.

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~TEST~NBM~~1~CL</GroupKey>
   </dgvViewAll_Vertical>
 </PWR_ViewAll>')

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


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 

Drop table #tmpData

Answer 1

Hard to believe, but the items ##3-4 are still missing.
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

So the guess game continues unabated.

There is still a mess around tokens inside the string. The provided DDL specifies a first token value (do we care about it?), but based on the verbiage explanation it suppose to be a 2nd token: Total Revenue.

declare @li varchar(max) ='Segment Detail'

SQL

DECLARE @xml XML
 , @li VARCHAR(MAX) ='Segment Detail'
 , @newXfundCode VARCHAR(MAX) ='newXfundCode'
 , @separator CHAR(1) = '~';

DECLARE @tmpData TABLE (id INT, xmldata XML);  

 INSERT INTO @tmpData(id, xmldata) VALUES 
 (1, 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~WOW~NBM~~1~CL</GroupKey>
 </dgvViewAll_Vertical>
</PWR_ViewAll>');

SET @xml= (SELECT xmldata FROM @tmpData WHERE ID=1);

;WITH rs AS
(
  SELECT col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)') AS [Before]
  FROM @xml.nodes('/PWR_ViewAll/dgvViewAll_Vertical') AS tab (col)
  --WHERE  CHARINDEX(@li, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)')) > 0
)
SELECT *, REPLACE(TRY_CAST('<root><r>' + 
 REPLACE([before], @separator, '</r><r>') + 
 '</r></root>' AS XML).query('<root>
{
   for $x in /root/r
   let $pos := count(/root/r[. << $x[1]]) + 1
   return if ($pos=3 and root/*[position() = 2 and ./text()="Total Revenue"]) then <r>{sql:variable("@newXfundCode")}</r>
   else $x
}
</root>').query('
   for $i in /root/r
   return if ($i is (/root/r[last()])[1]) then string($i)
  else concat($i, sql:variable("@separator"))
').value('.', 'NVARCHAR(MAX)'),@separator + SPACE(1), @separator) AS [After]
FROM rs;

Output

Before: Segment Detail~Total Revenue~WOW~NBM~~1~CL

After: Segment Detail~Total Revenue~newXfundCode~NBM~~1~CL

Answer 2

Hi @Sudip Bhatt ，

Sorry, I still don't know your specific question. But your title gives some hints, I found a similar case, maybe it can help you：
Replace value in XML using SQL

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html

Answer 3

I got another solution.

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 user*GroupKeyvalue varchar(100), 
    @newGroupKeyValue varchar(100);

SELECT *anonymous user*GroupKeyvalue = 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 user*groupkeyvalue 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 user*GroupKeyvalue, 
--starting from position of 2nd ~ +1
charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1)+1,
--as many characters as exist between 3rd ~ and 2nd ~ (excluding ~)
charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1)+1)-1
-
charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1),
--stuff the new xfundcode
@newXfundCode
);

--test
select *anonymous user*GroupKeyValue as oldgroupkeyvalue, @newGroupKeyValue as newgroupkeyvalue;


--test
select @xml as oldxml;

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

select @xml as newxml;


--update #tmpData set xmldata = @xml where ID=1;
--select * from #tmpData;

Drop table #tmpData

---

i could change the where clause (that have provided) to search/get the first groupkey value which has 'Total Revenue':

WHERE CHARINDEX(@li, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0; -->

WHERE CHARINDEX(@li+'~Total Revenue~', col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0;

Answer 4

I have update text in xml different way. full code.

DECLARE @xml xml
DECLARE @li varchar(max) ='Consensus Model'
DECLARE *anonymous user*XfundCode 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 user*GroupKeyvalue 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 user*XfundCode,@GrpKey) > 0
    BEGIN

        SET *anonymous user*GroupKeyValue=@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 user*GroupKeyValue")]/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