Updating JSON value in XML data SQL Server

Question

I have a simple XML which holds JSON as value in it.

<Columns>
  <Column Name="SID2" DataType="String">[{"LastUpdatedUtc":"2021-01-28T11:36:33.407"]</Column>
</Columns>

I am trying to update the json value in XML data. Name is coming dynamic and have to be created in an variable. I am trying this SQL to update the data with no luck. I think it is not parsing the @SID in it not sure how to make it work.

DECLARE @SID NVARCHAR(MAX) = 'SID2';
DECLARE @NewContent NVARCHAR(MAX) = '[{"LastUpdatedUtc":"'+CONVERT(varchar,GETUTCDATE(),126)+'"]';

UPDATE ABC
SET XML_DATA.modify('replace value of (/Columns/Column[@Name="{sql:variable("@SID")}"]/text()) [1] with sql:variable("@NewContent")')

question from:https://stackoverflow.com/questions/65936738/updating-json-value-in-xml-data-sql-server

Answer 1

You need a small change:

DECLARE @xml XML
SET @xml = '
   <Columns>
      <Column Name="SID2" DataType="String">[{"LastUpdatedUtc":"2021-01-28T11:36:33.407"]</Column>
   </Columns>
'
SELECT @xml AS Before

DECLARE @SID NVARCHAR(MAX)        = 'SID2';
DECLARE @NewContent NVARCHAR(MAX) = '[{"LastUpdatedUtc":"'+CONVERT(varchar,GETUTCDATE(),126)+'"]';
SET @xml.modify('
    replace value of (/Columns/Column[@Name=sql:variable("@SID")]/text())[1]
    with sql:variable("@NewContent")
')
SELECT @xml AS After

Results:

Before
<Columns><Column Name="SID2" DataType="String">[{"LastUpdatedUtc":"2021-01-28T11:36:33.407"]</Column></Columns>

After
<Columns><Column Name="SID2" DataType="String">[{"LastUpdatedUtc":"2021-01-28T13:10:24.850"]</Column></Columns>