With the below example, how do I escape the double-quote characters that are in the @data variable?
declare @data nvarchar(max) = N'"TEST"'; declare @jsonFragment nvarchar(max); declare @id int = 999; set @jsonFragment = ',"' + cast(@id as nvarchar(16)) + '":"' + @data + '"'; select @jsonFragment;
The result is:
I need it to be:
As I understand it, selecting
for json path requires that I define a static key name - but as can be seen, I have a scenario where key names are being defined dynamically.
In my real-world use case, this string construction is occurring within a
stuff statement, itself part of a column definition in a larger
select that utilises CTEs - meaning it would be very hard to create a dynamic sql statement to be run using