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="[{"Name":"ABCDEFGH","Age":1}]"></object></root>' SET @XML.modify('replace value of (/root/object/@attr)[1] with "[{"e;Name"e;:"e;ABCD"e;, "e;Age"e;: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="[{"e;Name"e;:"e;ABCD"e;, "e;Age"e;:1}]"> </object> </root>
I am not sure if i have completely explained scenario, pls help.
Try this:
DECLARE @XML XML = N'<root><object attr="[{"Name":"ABCDEFGH","Age":1}]"></object></root>' SET @XML.modify('replace value of (/root/object/@attr)[1] with string("[{"Name":"ABCD", "Age":1}]")') select @XML
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 "
or "e;
):
DECLARE @XML XML = N'<root><object attr="[{"Name":"ABCDEFGH","Age":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="[{"Name":"ABCD", "Age":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…