How to change the value in xml tag reading from select query result

Transact-SQL
1

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

> <Payments>
> <PaymentRecord>
>   <StatusCode>ACV</StatusCode>
>   <MethodDetail>
> 			<Check>
> 			  <BankName>JPMORGAN CHASE BANK</BankName>
> 			  <RoutingNumber>0187671</RoutingNumber>
> 			</Check>
>   </MethodDetail>
>   <ExtendedData>
> 			<Extra>
> 			  <Source>Bank</Source>
> 			  <PolicyNumber>12345677            </PolicyNumber>
> 			</Extra>
>   </ExtendedData>
>   <PostMarkDate />
>   <Amount>648.1000</Amount>
>   </PaymentRecord>
> </Payments>

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',
             '<Check>
			  <BankName>JPMORGAN CHASE BANK</BankName>
			  <RoutingNumber>0187671</RoutingNumber>
			</Check>', 
			'<Extra>
			  <Source>Bank</Source>
			  <PolicyNumber>12345677            </PolicyNumber>
			</Extra>', '',
			'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**

<Payments>
  <PaymentRecord>
    <StatusCode>ACV</StatusCode>
    <MethodDetail>
      <Check>
        <BankName>JPMORGAN CHASE BANK</BankName>
        <RoutingNumber>0187671</RoutingNumber>
      </Check>
    </MethodDetail>
    <ExtendedData>
      <Extra>
        <Source>Bank</Source>
        <PolicyNumber>10101010            </PolicyNumber>
      </Extra>
    </ExtendedData>
    <PostMarkDate>1900-01-01</PostMarkDate>
    <Amount>648</Amount>
  </PaymentRecord>
</Payments>
2

There a different ways to update XML:

Different ways to update XML using XQuery in SQL Server (sqlshack.com)

UPDATE Table1
SET ExtendedData.modify('replace value of (/Extra/PolicyNumber/text())[1] with "789012345"')
WHERE ExtendedData.value('(/Extra/Source)[1]','varchar(20)') = 'Bank'