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 文件？

您可以使用該for xml path('')技巧在遞歸函式中直接連接您需要的字元串。

這樣的事情應該為你做：

create function dbo.SelectChild(@name as varchar(99)) returns varchar(max)
begin
return 
(
 select ',"children":[' + 
          stuff((
                select ',{"name":"' + 
                         E.employee + '"'+
                         isnull(dbo.SelectChild(E.employee), '')+'}'
                from dbo.emp_mgr as E
                where E.manager = @name
                for xml path(''), type
                ).value('text()[1]', 'varchar(max)'), 1, 1, '')+']'
)
end

go

select '{"name":"' + E.employee + '"'+isnull(dbo.SelectChild(E.employee), '')+'}'
from dbo.emp_mgr as E
where E.manager is null

結果：

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

引用自：https://dba.stackexchange.com/questions/105737