SQLTeam.com | Weblogs | Forums

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

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]
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',
			  <BankName>JPMORGAN CHASE BANK</BankName>
			  <PolicyNumber>12345677            </PolicyNumber>
			</Extra>', '',

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

        <BankName>JPMORGAN CHASE BANK</BankName>
        <PolicyNumber>10101010            </PolicyNumber>

There a different ways to update XML:

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

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