sql org chart分层表到xml json

我有一张员工和经理表：

create table emp_mgr (emp varchar(99), mgr varchar(99))

insert into emp_mgr select 'Adam',NULL; insert into emp_mgr select 'Bob','Adam'; insert into emp_mgr select 'Charles','Bob'; ; insert into emp_mgr select 'David','Bob'; insert into emp_mgr select 'Ethan','Bob'; insert into emp_mgr select 'Frank','Charles'; insert into emp_mgr select 'George','Charles'; insert into emp_mgr select 'Henry','Charles'; insert into emp_mgr select 'Ian','David'; insert into emp_mgr select 'James','Ethan'; insert into emp_mgr select 'Ken','Ethan'; insert into emp_mgr select 'Larry','Ethan'; insert into emp_mgr select 'Mike','Frank'; insert into emp_mgr select 'Ned','Frank'; insert into emp_mgr select 'Oliver','Frank'; insert into emp_mgr select 'Peter','Frank'; insert into emp_mgr select 'Quinn','George'; insert into emp_mgr select 'Robert','George'; insert into emp_mgr select 'Scott','George'; insert into emp_mgr select 'Ted','George'; insert into emp_mgr select 'Urban','George'; insert into emp_mgr select 'Victor','Henry'; insert into emp_mgr select 'Wes','Ian'; insert into emp_mgr select 'Xavier','James'; insert into emp_mgr select 'Young','James'; insert into emp_mgr select 'Zach','James'; 

我想从中生成这个 json 文件：

{ name:"Adam" , children:[   { name:"Bob" , children:[     { name:"Charles" , children:[       { name:"Frank" , children:[         { name:"Mike" },         { name:"Ned" },         { name:"Oliver" },         { name:"Peter" }       ]},       { name:"George" , children:[         { name:"Quinn" },         { name:"Robert" },         { name:"Scott" },         { name:"Ted" },         { name:"Urban" }       ]},       { name:"Henry" , children:[         { name:"Victor" }       ]}     ]},     { name:"David" , children:[       { name:"Ian" , children:[         { name:"Wes" }       ]}     ]},     { name:"Ethan" , children:[       { name:"James" , children:[         { name:"Xavier" },         { name:"Young" },         { name:"Zach" }       ]},       { name:"Ken" },       { name:"Larry" }     ]}   ]} ]}

这是我尝试过的 - 使用递归 xml 查询..

CREATE function [dbo].[SelectChild](@name as varchar(99))
returns xml
begin
    return (
        select 
            employee as "@name",
            dbo.SelectChild(employee)
        from emp_mgr
        where manager = @name
        for xml path('record'), elements
    )
end

select 
    employee as "@name", 
    dbo.SelectChild(employee)
from emp_mgr
where manager is null
for xml path ('record')

..我能够生成这个 xml ：

<record name="Adam">   <record name="Bob">     <record name="Charles">       <record name="Frank">         <record name="Mike" />         <record name="Ned" />         <record name="Oliver" />         <record name="Peter" />       </record>       <record name="George">         <record name="Quinn" />         <record name="Robert" />         <record name="Scott" />         <record name="Ted" />         <record name="Urban" />       </record>       <record name="Henry">         <record name="Victor" />       </record>     </record>     <record name="David">       <record name="Ian">         <record name="Wes" />       </record>     </record>     <record name="Ethan">       <record name="James">         <record name="Xavier" />         <record name="Young" />         <record name="Zach" />       </record>       <record name="Ken" />       <record name="Larry" />     </record>   </record> </record>

但不知道如何在其中获取“儿童”标签，然后如何将其转换为 json 格式？

如何使用 SQL 从分层组织数据生成 json 文件？