How to modify JSON data in XML attribute in SQL server, if XML is having attribute which contains JSON like data having special characters

I have a very huge data to be updated but i’m stuck while modifying XML. i need to use modify(replace value of "" with ""). if i am using normal string it is modifying that data in XML, but in case of special characters it’s saying "invalid entity reference using special characters".

DECLARE @XML XML = N'<root><object attr="[{&quot;Name&quot;:&quot;ABCDEFGH&quot;,&quot;Age&quot;:1}]"></object></root>' SET @XML.modify('replace value of (/root/object/@attr)[1] with "[{&quote;Name&quote;:&quote;ABCD&quote;, &quote;Age&quote;:1}]"') select @XML 

I wanted to update this string as it is in that @attr with "" just for start and end of that string.

Ex:

<root>    <object attr="[{&quote;Name&quote;:&quote;ABCD&quote;, &quote;Age&quote;:1}]">    </object> </root> 

I am not sure if i have completely explained scenario, pls help.

Add Comment
2 Answer(s)

Try this:

DECLARE @XML XML = N'<root><object attr="[{&quot;Name&quot;:&quot;ABCDEFGH&quot;,&quot;Age&quot;:1}]"></object></root>' SET @XML.modify('replace value of (/root/object/@attr)[1] with string("[{&quot;Name&quot;:&quot;ABCD&quot;, &quot;Age&quot;:1}]")') select @XML 
Add Comment

In my eyes the best was not to hassle with string escaping at all. This will all be done implitly (besides the fact, that it is a difference if you use &quot; or &quote;):

DECLARE @XML XML = N'<root><object attr="[{&quot;Name&quot;:&quot;ABCDEFGH&quot;,&quot;Age&quot;:1}]"></object></root>'  --We can use a *normal* string DECLARE @newContent NVARCHAR(MAX)=N'[{"Name":"ABCD", "Age":1}]';  --We can place this string using `sql:variable()` SET @XML.modify('replace value of (/root/object/@attr)[1] with sql:variable("@newContent")')  SELECT @XML; 

The result

<root>   <object attr="[{&quot;Name&quot;:&quot;ABCD&quot;, &quot;Age&quot;:1}]" /> </root> 

As you can see, the escaping is done for you implicitly…

Hint: Trying to solve this yourself might stumble over some special characters you did not think about. This may pass all your tests and break in production… Worst case…

Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.