SQL - How to change the value in xml tag reading from select query res

Hi Team,

I have written a query to read the data from Table1 table , in that table there are 2 columns which has xml values, so I need to change one of the xml element value with the new value (it doesn’t matter what value is already present)

My query:-

SELECT [StatusCode]
      ,MethodDetail
      ,[ExtendedData]
      ,[PostMarkDate]
      ,[Amount]
FROM [dbo].[Table1]
FOR XML RAW('PaymentRecord'), ELEMENTS, TYPE, ROOT('Payments')

My current result :-



  ACV
  
       
           JPMORGAN CHASE BANK
           0187671
       
  
  
       
           Bank
           12345677
       
  
  
  648.1000

Here I need change the PolicyNumber element value to my own value like '76576566' - I will not be knowing what value present in the table - but I know new value which needs to be changed.

Kindly let me know how to perform this.

This is the sample table with the data :-

Create table [Table1]
(
[StatusCode] nvarchar(10),
[MethodDetail] xml,
[ExtendedData] XML,
[PostMarkDate] DATE,
[Amount] DECIMAL
)

insert into [Table1]([StatusCode],[MethodDetail],[ExtendedData],[PostMarkDate],[Amount]) values ('ACV',
             '
                      JPMORGAN CHASE BANK
                      0187671
                    ',
                    '
                      Bank
                      12345677
                    ', '',
                    '648.1000'
                    )

Expected Output could be : I just need to display in my select query result with new policy number - no update to the Table


  
    ACV
    
      
        JPMORGAN CHASE BANK
        0187671
      
    
    
      
        Bank
        10101010
      
    
    1900-01-01
    648

DECLARE @tbl TABLE ( [StatusCode] VARCHAR(10), [MethodDetail] XML, [ExtendedData] XML, [PostMarkDate] DATE, [Amount] DECIMAL ); INSERT INTO @tbl ([StatusCode],[MethodDetail],[ExtendedData],[PostMarkDate],[Amount]) values ('ACV', N' JPMORGAN CHASE BANK 0187671 ', N' Bank 12345677 ', '', '648.1000'); DECLARE @PolicyNumber VARCHAR(20) = '76576566'; DECLARE @outXML XML = ( SELECT [StatusCode] ,MethodDetail ,[ExtendedData] ,[PostMarkDate] ,[Amount] FROM @tbl FOR XML RAW('PaymentRecord'), ELEMENTS, TYPE, ROOT('Payments')); SET @outXML.modify('replace value of (/Payments/PaymentRecord/ExtendedData/Extra/PolicyNumber/text())[1] with sql:variable("@PolicyNumber")'); -- test SELECT @outXML;

SELECT [StatusCode] ,MethodDetail ,ExtendedData.query('{ for $e in /Extra/* return if (local-name($e)="PolicyNumber") then {sql:variable("@PolicyNumber")} else $e }') ,[PostMarkDate] ,[Amount] FROM @tbl FOR XML RAW('PaymentRecord'), ELEMENTS, TYPE, ROOT('Payments');