Update XML element value with new values in SQL Script [duplicate]

Question

I have XML in one of the column in the XYZ Table, here i Need to update Amount tag with new value instead of 0.00 and then PolicyReference and AccountReference I need to update two different values in these tags instead of Blank . For example -

 <PolicyReference>7657576567</PolicyReference>
                  <AccountReference>7657576875</AccountReference>

This is my XML inside the column :-

<document>
  <StatusCode>ACV</StatusCode>
  <PaymentMethodDetail>
    <EFT>
      <AccountNumber>123456789</AccountNumber>
      <AccountName>ABCDEFGHIJK</AccountName>
    </EFT>
  </PaymentMethodDetail>
  <PaymentExtendedData>
    <CHECK>
      <Source>System</Source>
      <SourceType>ACH</SourceType>
    </CHECK>
  </PaymentExtendedData>
  <PostMarkDate />
  <EntryUserId>1</EntryUserId>
  <Amount>0.00</Amount>
  <EntryDate />
  <PolicyLineOfBusiness>LOL</PolicyLineOfBusiness>
  </PolicyReference>
  </AccountReference>
  <AccountId>2034001793</AccountId>
</document>

This is what I have tried :-

Update XYZ
SET XmlPayload.modify('replace value of (//document/PolicyReference/)[1] with "<PolicyReference>275654</PolicyReference>"')
where PaymentSearchId =18785

I am getting an error message, please suggest me :- Msg 9341, Level 16, State 1, Line 4 XQuery [XYZ.XmlPayload.modify()]: Syntax error near ')', expected a step expression.

You should edit your previous question, not create a new question. — Dour High Arch
– Dour High Arch, Commented Oct 2, 2020 at 17:46

T.S. · Accepted Answer · 2020-10-02 18:58:15Z

1

This should do it - use the string Replace to do it with string because your XML is not a valid XML and XML functionality will not work for it. But string will

UPDATE XYZ
SET XmlPayload = REPLACE(CAST(XmlPayload AS NVARCHAR(MAX)), ' </PolicyReference>', '<PolicyReference>7657576567</PolicyReference>')
WHERE PaymentSearchId =18785

edited Oct 2, 2020 at 18:58

answered Oct 2, 2020 at 17:46

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user613400 Over a year ago

No, it will not work, as i showed in the XML , current XML tag is like this - </PolicyReference>, so now i need to update like this - <PolicyReference> 275654</PolicyReference>

user613400 Over a year ago

I mean I need to add complete XML tag like starting and ending of that tag

T.S. Over a year ago

@user613400 Oh... well. I see. Then you out of luck with XML because that "xml" is not xml or invalid xml. BUT you are not out of luck completele - SEE MY updated answer

Collectives™ on Stack Overflow

Update XML element value with new values in SQL Script [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related