sql org chart hierarchical table to xml json

Question

I have a table of employees and managers:

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'; 

and I want to generate this json file from it :

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

Here's what I tried - With a recursive xml query..

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')

..I was able to generate this 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>

but don't know how to get the 'children' tag in there, and then how to convert it to json format ?

How do i generate a json file from hierarchical organization data using SQL ?

Answer 1

You can use the for xml path('') trick to concatenate the strings you need directly in the recursive function.

Something like this should do it for you:

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

Result:

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