In SQL Server : how to change the value in xml tag before reading from select query

BSSwathi 0
Viewed 49

I have written a query to read the data from Table1. In that table there are 2 columns which have 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>

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 (my current SQL version is v18.11.1):

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 should 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>76576566</PolicyNumber>
      </Extra>
    </ExtendedData>
    <PostMarkDate>1900-01-01</PostMarkDate>
    <Amount>648</Amount>
  </PaymentRecord>
</Payments>
.net sql-server sql
1 Answers

Please try the following solution.

SQL #1

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

SQL #2

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

Output

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