How to Update XML tags value with new values in SQL Script

Question

This is my 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 diffrent values in these tags instead of Blank .   For example:- 7657576567 7657576875   This is my XML document 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 >    Could you please let me now How do I achive this in SQL query

Mageshwaran R · Answer

Hi Kumar , please refer this link: https://www.sqlshack.com/different-ways-to-update-xml-using-xquery-in-sql-server/

Jignesh Kumar · Answer

Hi Kumar,   Please refer this link, first try select Statement and then update statement. You will come to know what values you are getting in select statement,   https://stackoverflow.com/questions/1440666/update-xml-node-value-in-sql-server

Sachin Singh · Answer

UPDATE [TableName]      SET           [fieldName].modify( 'replace value of (root/elementName/text())[1] with "yourValue"' )   GO