Riley | Criando um framework para fins de aprendizado | Application Framework library

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'


  ZB-P1
  B. Riley FBR Inc.
  08-21-2020
  Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1


  CL
  Deutsche Bank
  02-28-2020
  Segment Detail~Total Revenue~RD_100~NBM~~1~CL

')

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

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

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

--test
select @oldGroupKeyValue as oldgroupkeyvalue, @newGroupKeyValue as newgroupkeyvalue;


--test
select @xml as oldxml;

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

select @xml as newxml;


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

Drop table #tmpData