Oct 16, 2015

Script to generate output in JSON format SQL Server

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 ', '{"'), '/>', '},'), '="', '":"'), '" ', '","'), '\', '\\'), '&quot;', '\"') + ']', '"},]', '"}]')


For example:

SELECT REPLACE('[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST((
                                                            SELECT *
                                                            FROM (
                                                                  SELECT *
                                                                  FROM school
                                                                  ) DT
                                                            FOR XML RAW
                                                            ) AS VARCHAR(MAX)), '<row ', '{"'), '/>', '},'), '="', '":"'), '" ', '","'), '\', '\\'), '&quot;', '\"') + ']', '"},]', '"}]')


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