SQL Server: How to speed up data updating in xml

Question

I have two column in a table where stored large xml. when i am updating data by xquery then it is taking long time. so i am not sure my approach is right or wrong. please see my script and guide me how to refactor the code to speed up updating data in xml.

CREATE TABLE [dbo].[tblTestXml](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [XmlData] [xml] NULL,
    [XmlComments] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

two sample xml which is small for positing here but my real xml is big.

Sample 1

<?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>

Sample 2

<?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>

Here my script which i am using to update data in xml

ALTER PROC USP_UpdateLineItemInXML
(
    @FromLineItem VARCHAR(MAX),
    @ToLineItem VARCHAR(MAX)
)
AS

DECLARE @UPDATE_STATUS BIT = 0
DECLARE @TmpFromLineItem VARCHAR(MAX)
DECLARE @TmpToLineItem VARCHAR(MAX)

BEGIN

  BEGIN --Finding linetem in GroupKey by name of QcViewAll xml. if found then update lineitem in group key
      SET @TmpFromLineItem='~'+@FromLineItem+'~'
      SET @TmpToLineItem='~'+@ToLineItem+'~'

      IF EXISTS(SELECT * FROM tblTestXml
      WHERE XmlData.exist('/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@TmpFromLineItem"))]')>0)
      BEGIN
        SET @UPDATE_STATUS = 1
      END

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

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

    BEGIN --Finding linetem in Lineitem tag by name of comments xml. if found then update lineitem in Lineitem tag
        SET @UPDATE_STATUS = 0

        IF EXISTS(SELECT * FROM tblTestXml
        WHERE XmlComments.exist('/ArrayOfClsCommentPopup/clsCommentPopup/LineItem[text() = sql:variable("@FromLineItem")]')>0)
        BEGIN
            SET @UPDATE_STATUS = 1
        END

        WHILE @UPDATE_STATUS > 0
        BEGIN
            UPDATE t
            SET XmlComments.modify('replace value of (/ArrayOfClsCommentPopup/clsCommentPopup/LineItem[text() = sql:variable("@FromLineItem")]/text())[1]
            with (sql:variable("@ToLineItem"))')
            FROM tblTestXml AS t
            WHERE XmlComments.exist('/ArrayOfClsCommentPopup/clsCommentPopup/LineItem[text() = sql:variable("@FromLineItem")]') = 1;

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

exec USP_UpdateLineItemInXML 'Total Revenue','Gross Revenue'

Please suggest a performant script which get me rid of slow data updating problem.

EDIT
I assume my second update taking long time. the moment i change my script to update data this way

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

my XmlComments updation take less than a second when i replace my second script with above one.

Please give me a right direction. what is wrong in my second script Thanks

Answer 1

Making updates to XML documents en masse is no fun. It may be better to extract all the XML data into temp tables, perform the updates and then create new XML documents and write these back to the source tables.