SQL Server: Escape single quotes, double quotes, XML and forward slashes in JSON String

March 23, 2022

If you are writing the T-SQL directly then, given a valid JSON text you need to:

Surround it with single quotes.
Escape any single quotes in it
Escape any escape sequences

So the JSON:

[1, "Kennedy O'Brian", { "hello": "world" }]

Would become

Messages='[1, "Kennedy O\'Brian", { "hello": "world" }]'

Example without STRING_ESCAPE:
Check example below with xml structure data where I added single quotes, double quotes, forward slashes in the JSON string and IsJSON is also false.

DECLARE @str VARCHAR(800) = '<root>
<blog lastUpdated="03/22/2022">kennedyihe.com</blog>
<author>kennedy</author>
</root>'
DECLARE @json VARCHAR(800) = '[{"Name":"Kennedy","Gender":"Male","About":"'[email protected]+'"}]'
 
SELECT @json as Json
SELECT ISJSON(@json) IsJson

Result

Json
----------------------------------------
[{"Name":"Kennedy","Gender":"Male","About":"<root>          <blog lastUpdated="03/22/2022">kennedyihe.com</blog>          <author>kennedy</author>         </root>"}]

IsJson
----------------------------------------
0

Example with STRING_ESCAPE:
In the below example, check the result of STRING_ESCAPE() and IsJSON is also true. STRING_ESCAPE() requires two parameters of value and escape_type. The escape type here will be ‘JSON’.

DECLARE @str VARCHAR(800) = '<root>
<blog lastUpdated="03/22/2022">kennedyihe.com</blog>
<author>kennedy</author>
</root>'
DECLARE @json VARCHAR(800) = '[{"Name":"Kennedy","Gender":"Male","About":"'+ STRING_ESCAPE(@str, 'JSON')+'"}]'
 
SELECT @json as Json
SELECT ISJSON(@json) IsJson

Result

Json
----------------------------------------
[{"Name":"Kennedy","Gender":"Male","About":"<root>\r\n\t\t\t\t\t\t\t\t<blog lastUpdated=\"03\/22\/2022\">kennedyihe.com<\/blog>\r\n\t\t\t\t\t\t\t\t<author>kennedy<\/author>\r\n\t\t\t\t\t\t\t<\/root>"}]

IsJson
----------------------------------------
1

Enjoy!