SQL Query
to convert result set in JSON FORMAT in Sql server 2005, 2008, 2012
SELECT REPLACE('[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST((
SELECT *
FROM (
/*<Replace With Your SELECT Statement>*/
) DT
FOR XML RAW
) AS VARCHAR(MAX)), '<row ', '{"'), '/>', '},'), '="', '":"'), '" ', '","'), '\', '\\'), '"', '\"') + ']', '"},]', '"}]')
For
example:
SELECT REPLACE('[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST((
SELECT *
FROM (
SELECT *
FROM school
) DT
FOR XML RAW
) AS VARCHAR(MAX)), '<row ', '{"'), '/>', '},'), '="', '":"'), '" ', '","'), '\', '\\'), '"', '\"') + ']', '"},]', '"}]')
Sample
output:
[
{"StuId":"25","StuName":"raja\"kumar","PhnNo":"k\"\"l"},
{"StuId":"59","StuName":"ram","PhnNo":"9136784964"},
{"StuId":"95","StuName":"hari","PhnNo":"9136784964"},
{"StuId":"251","StuName":"raja\\kumar","PhnNo":"k\\\\l"}
]
No comments:
Post a Comment