SQL Server How to update data in xml

Question

See my xml

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfClsCommentPopup xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <clsCommentPopup>
    <InternalComment>Non conforming figure of $2,559.6  million provided by the broker is not reconcilable. Hence ignored.</InternalComment>
    <BrokerFor>4E</BrokerFor>
    <Formula />
    <ModelValue />
    <ExternalComment>Non conforming figure of $2,559.6  million provided by the broker is not reconcilable. Hence ignored.</ExternalComment>
    <StartPeriod />
    <EndPeriod />
    <FollowUP>false</FollowUP>
    <ThisPeriod>false</ThisPeriod>
    <IgnoreValue>true</IgnoreValue>
    <LineItem>Total Revenue</LineItem>
    <Section>Consensus Model</Section>
    <QcPeriod />
    <PeriodType />
    <PeriodCollection>2012 FY</PeriodCollection>
    <IgnoreData />
  </clsCommentPopup>
  <clsCommentPopup>
    <InternalComment>Non conforming figure of $920.31 million provided by the broker is not reconcilable. Hence ignored.</InternalComment>
    <BrokerFor>4E</BrokerFor>
    <Formula />
    <ModelValue />
    <ExternalComment>Non conforming figure of $920.31 million provided by the broker is not reconcilable. Hence ignored.</ExternalComment>
    <StartPeriod />
    <EndPeriod />
    <FollowUP>false</FollowUP>
    <ThisPeriod>false</ThisPeriod>
    <IgnoreValue>true</IgnoreValue>
    <LineItem>Total Revenue</LineItem>
    <Section>Consensus Model</Section>
    <QcPeriod />
    <PeriodType />
    <PeriodCollection>4Q 2015</PeriodCollection>
    <IgnoreData />
  </clsCommentPopup>
  </ArrayOfClsCommentPopup>

I want to find and replace text with in <LineItem> tag. if there are many line item like <LineItem>Total Revenue</LineItem> then all Total Revenue will be Gross Revenue

I have done the job this way and it worked. suppose above xml stored in a table called tbltestxml and i replace this way which worked.

    update tbltestxml
    SET XmlComments = REPLACE(CAST(XmlComments AS NVARCHAR(MAX)), '<LineItem>Total Revenue</LineItem>', '<LineItem>Total Revenue_1</LineItem>')
    where id=1

i like to know how many other way exist to replace this text Total Revenue to Gross Revenue in xml. please discuss all ways with sample code. thanks

Answer 1

Hi @T.Zacks ,

Please try the following solution.

You need to provide a minimal reproducible example.
I removed unnecessary XML elements irrelevant to the task.

SQL Server XQuery .modify() method can update just one XML element at a time. That's why I am using a loop to update multiple XML elements in question.

XQuery version 3.0/3.1 can update multiple XML elements in one single shot.
Microsoft yet to implement it support in MS SQL Server.
That's one of the reasons for my suggestion to Microsoft: SQL Server vNext (post 2019) and NoSQL functionality

-- DDL and sample data population, start  
 DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);  
 INSERT INTO @tbl (xmldata) VALUES  
 (N'<ArrayOfClsCommentPopup xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
 <clsCommentPopup>  
 <LineItem>Total Revenue</LineItem>  
 </clsCommentPopup>  
 <clsCommentPopup>  
 <LineItem>Total Revenue</LineItem>  
 </clsCommentPopup>  
</ArrayOfClsCommentPopup>');  
 -- DDL and sample data population, end  
  
DECLARE @from VARCHAR(30) = 'Total Revenue'  
    , @to VARCHAR(30) = 'Gross Revenue';  
      
 -- before  
 SELECT * FROM @tbl  
 WHERE xmldata.exist('/ArrayOfClsCommentPopup/clsCommentPopup/LineItem[text() = sql:variable("@from")]') = 1;  
      
 DECLARE @UPDATE_STATUS BIT = 1;  
      
 WHILE @UPDATE_STATUS > 0  
 BEGIN  
    UPDATE t  
    SET xmldata.modify('replace value of (/ArrayOfClsCommentPopup/clsCommentPopup/LineItem[text() = sql:variable("@from")]/text())[1]  
       with (sql:variable("@to"))')  
    FROM @tbl AS t  
    WHERE xmldata.exist('/ArrayOfClsCommentPopup/clsCommentPopup/LineItem[text() = sql:variable("@from")]') = 1;  
          
    SET @UPDATE_STATUS = @@ROWCOUNT;  
    PRINT @UPDATE_STATUS;  
 END;  
      
 -- after  
 SELECT * FROM @tbl;