I'm aware of the option to output a query formatted as JSON, like the following example from the from MSDN page:
SELECT name, surname
FROM emp
FOR JSON AUTO
There are a lot of samples on how to use the resulting json from apps but my question is, how can I store the resulting json in a varchar variable, let's say to store in another table?
3条答案
按热度按时间avwztpqn1#
Dan Guzman replied in the MSDN Forum with this neat solution, which corresponds also to @FDavidov's suggestion in his last comment
aurhwmvo2#
A JSON is, in fact, a character string. What makes this character string to be a JSON is the combination of two things:
So, if you want to store a JSON in a variable, just assign to the variable the source string and, whenever you want to act on your variable, just remember it is a JSON.
jv2fixgn3#
I was looking for a similar thing, but how to use the JSON PATH output data from SQL Server in an application. In this case, in Node.JS (although this concept would be adaptable to other languages). Here's a sample of what I came up with:
This avoids it being returned as a long string in a UUID field like:
And is instead something you can use in the application.