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"}

]