Kumar AU

Kumar AU

  • 1.3k
  • 292
  • 55.2k

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

Sep 23 2022 4:27 PM

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>

 


Answers (1)