Replace XML data in SQL Server

Using the XML modify method is the most efficient way to modify XML data stored in xml type variable or column. This method takes an XML DML statement to insert, replace, or delete nodes from XML data. Here is an example of using XML DML to replace the value of an attribute in XML documents stored in a xml type column.

DECLARE @old_tkr nvarchar(35), @new_tkr nvarchar(35)
-- SET @old_tkr = 'ABC'
-- SET @new_tkr = 'XYZ'
;WITH XMLNAMESPACES(DEFAULT 'http://schemas.guosen.com.hk/sqlserver/2014/04/guosen-archive/AnalystReportMetadata')
UPDATE AnalystReportMetaData SET
metadataXml.modify('replace value of (/analystreport/stocks/stock[@code=sql:variable("@old_tkr")]/@code)[1] with sql:variable("@new_tkr")')
WHERE metadataXml.exist('/analystreport/stocks/stock[@code=sql:variable("@old_tkr")]') = 1

Sample XML:
<analystreport xmlns="http://schemas.guosen.com.hk/sqlserver/2014/04/guosen-archive/AnalystReportMetadata">
  <stocks>
    <stock guosenNum="1617" code="1302 HK" countryCode="CN" marketCode="HK"></stock>
  </stocks>
</analystreport>