‫گرفتن خروجی JSON از جداول در SQL Server 2012

declare @t table(id int, name nvarchar(max), active bit)
insert @t values (1, 'Group 1', 1), (2, 'Group 2', 0)

select '[' + STUFF((
        select 
            ',{"id":' + cast(id as varchar(max))
            + ',"name":"' + name + '"'
            + ',"active":' + cast(active as varchar(max))
            +'}'

        from @t t1
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') + ']'

[{"id":1,"name":"Group 1","active":1},{"id":2,"name":"Group 2","active":0}]

declare @group table(id int, name nvarchar(max), active bit)
insert @group values (1, 'Group 1', 1), (2, 'Group 2', 0)

declare @member table(id int, groupid int,name nvarchar(max))
insert @member values (1, 1,'Ali'), (2, 1,'Mojtaba'),(3,2,'Hamid')

select '[' + STUFF((
        select 
            ',{"id":' + cast(g.id as varchar(max))
            + ',"name":"' + g.name + '"'
+ ',"members": { "children": [' + 
(select + STUFF((
select 
 ',{"id":' + cast(m.id as varchar(max))
+ ',"name":"' + m.name + '"}'
from @member m
where m.groupid = g.id
for xml path(''), type
 ).value('.', 'varchar(max)'), 1, 1, '')

 + ']}'
            + ',"active":' + cast(g.active as varchar(max))
            +'}')

        from @group g 
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') + ']'

[{"id":1,"name":"Group 1","members": 
     { "children": [{"id":1,"name":"Ali"},{"id":2,"name":"Mojtaba"}]}
,"active":1},
{"id":2,"name":"Group 2","members": 
      { "children": [{"id":3,"name":"Hamid"}]}
,"active":0}]