1

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 ?

  • SQL Server 2016 is the only version that supports JSON formatting natively. – Max Vernon Jul 2 '15 at 3:54
  • Why are you creating a function? You likely should be creating a stored procedure or simple a view instead. – Max Vernon Jul 2 '15 at 3:56
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.