Json String in Sql Server

In this post i will show how to display/Convert Table Rows to JSON String.

Continuing the previous Post Create Temp Table in Sql Server

OutPut:

{

                "Data": [{

                                "id": 1,

                                "name": "Kiran",

                                "active": 1

                }, {

                                "id": 2,

                                "name": "Ravi",

                                "active": 1

                }]

}

 select '{"Data":[' + STUFF((
        select
            ',{"id":' + cast(ID as varchar(50))
            + ',"name":"' + Name + '"'
            + ',"active":' + cast(IsActive as varchar(5))
            +'}'
        from @Tbl t1
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') + ']}'