JSON, XML, YAML and HTML are great for recording hierarchies such as organisations, taxonomies, and parts lists. How do we output structured document fragments to show a hierarchical list using SQL? I was hoping that the advent of JSON to SQL Server would make this easier but I found its use frustrating to the point that I keep it as arms-length as possible.
Because I would have found it useful myself, I’ve recorded here how to use T-SQL to get the four main types of document types to represent a simple hierarchical list in SQL Server.
First, before we do anything else, we’ll create some test data. In this example, I’ll steal the employee hierarchy from AdventureWorks2014, and put it in a test table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
--if necessary delete the existing table IF Object_Id('staff', 'U') IS NOT null DROP TABLE Staff; GO --create our staff hierarchy table CREATE TABLE Staff ( OrganizationNode HIERARCHYID PRIMARY KEY, employee VARCHAR(80) ); --steal the data from Adventureworks INSERT INTO Staff (OrganizationNode, employee) SELECT Employee.OrganizationNode, Coalesce(Person.Title+' ','')+ Person.FirstName+' ' +Coalesce(Person.MiddleName+' ','') + Person.LastName+Coalesce(' '+Person.Suffix,'')+ '. '+ Employee.JobTitle FROM AdventureWorks2014.HumanResources.Employee Employee INNER JOIN AdventureWorks2014.Person.Person person ON Employee.BusinessEntityID = Person.BusinessEntityID WHERE OrganizationNode IS NOT null; GO |
First, we’ll tackle a JSON rendering of the hierarchy
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
IF Object_Id('dbo.JSONHierarchicalListOfReports', 'FN') IS NOT null DROP FUNCTION dbo.JSONHierarchicalListOfReports; GO /* create the function that creates a JSON representation of the hierarchy */ CREATE FUNCTION JSONHierarchicalListOfReports(@Me HIERARCHYID) RETURNS VARCHAR(8000) BEGIN RETURN (SELECT Coalesce((SELECT '{"'+ String_Escape(me.employee, 'json')+'": ['+Stuff( --get a list of reports (SELECT ',' + dbo.JSONHierarchicalListOfReports(reports.OrganizationNode) FROM Staff AS reports WHERE reports.OrganizationNode.GetAncestor(1) = me.OrganizationNode FOR XML PATH(''), TYPE ).value('.', 'varchar(max)'),1, 1,'')+']}' ),'"'+String_Escape(me.employee, 'json')+'"') AS reports FROM Staff AS me WHERE me.OrganizationNode = @Me ); END; GO |
We can now try it out …
|
1 2 3 |
SELECT dbo.JSONHierarchicalListOfReports(OrganizationNode) FROM staff WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager'; |
To get this JSON rendition (after prettifying to make it easier to read)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
{ "Roberto Tamburello. Engineering Manager": [ "Rob Walters. Senior Tool Designer", "Ms. Gail A Erickson. Design Engineer", "Mr. Jossef H Goldberg. Design Engineer", { "Dylan A Miller. Research and Development Manager": [ "Diane L Margheim. Research and Development Engineer", "Gigi N Matthew. Research and Development Engineer", "Michael Raheem. Research and Development Manager" ] }, { "Ovidiu V Cracium. Senior Tool Designer": [ "Thierry B D'Hers. Tool Designer", "Ms. Janice M Galvin. Tool Designer" ] }, "Michael I Sullivan. Senior Design Engineer", "Sharon B Salavaria. Design Engineer" ] } |
So, emboldened, we try YAML. It turns out to be very easy as there is no support for it in SQL Server
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
IF Object_Id('dbo.YAMLHierarchicalListOfReports', 'FN') IS NOT null DROP FUNCTION dbo.YAMLHierarchicalListOfReports; GO /* create the function that creates a YAML representation of the hierarchy */ CREATE FUNCTION YAMLHierarchicalListOfReports(@Me HIERARCHYID, @indent INT =0) RETURNS VARCHAR(8000) BEGIN RETURN (SELECT Coalesce((SELECT Space(@indent)+CASE WHEN @indent=0 THEN '' ELSE '- ' END +String_Escape(me.employee, 'json')+': '+Stuff( --get a list of reports (SELECT ' ' + dbo.YAMLHierarchicalListOfReports(reports.OrganizationNode,@indent+4) FROM Staff AS reports WHERE reports.OrganizationNode.GetAncestor(1) = me.OrganizationNode FOR XML PATH(''), TYPE ).value('.', 'varchar(max)'),1, 1,'') ),space(@indent)+'- '+String_Escape(me.employee, 'json')) AS reports FROM Staff AS me WHERE me.OrganizationNode = @Me ); END; GO |
This will render the same manager and his reports even more simply, and doesn’t need prettifying to understand
|
1 2 3 4 |
SELECT '--- ' + dbo.YAMLHierarchicalListOfReports(OrganizationNode,DEFAULT) FROM staff WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager'; |
This, when executed gives the following YAML document …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--- Roberto Tamburello. Engineering Manager: - Rob Walters. Senior Tool Designer - Ms. Gail A Erickson. Design Engineer - Mr. Jossef H Goldberg. Design Engineer - Dylan A Miller. Research and Development Manager: - Diane L Margheim. Research and Development Engineer - Gigi N Matthew. Research and Development Engineer - Michael Raheem. Research and Development Manager - Ovidiu V Cracium. Senior Tool Designer: - Thierry B D'Hers. Tool Designer - Ms. Janice M Galvin. Tool Designer - Michael I Sullivan. Senior Design Engineer - Sharon B Salavaria. Design Engineer |
The XML version is pretty simple …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE FUNCTION xmlHierarchicalListOfReports(@Me HIERARCHYID) RETURNS XML BEGIN RETURN (SELECT me.employee AS "@name", (SELECT dbo.xmlHierarchicalListOfReports(reports.OrganizationNode) FROM Staff AS reports WHERE reports.OrganizationNode.GetAncestor(1) = me.OrganizationNode FOR XML raw('') , ROOT('reports'), ELEMENTS, TYPE ) FROM staff AS me WHERE me.OrganizationNode = @Me FOR XML path('employee') ); END; GO |
… and we can execute it like this …
|
1 2 3 |
SELECT dbo.xmlHierarchicalListOfReports(OrganizationNode) FROM staff WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager'; |
… to give the following XML (prettified to make it easier to read) …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<?xml version="1.0" encoding="UTF-8"?> <employee name="Roberto Tamburello. Engineering Manager"> <reports> <employee name="Rob Walters. Senior Tool Designer" /> <employee name="Ms. Gail A Erickson. Design Engineer" /> <employee name="Mr. Jossef H Goldberg. Design Engineer" /> <employee name="Dylan A Miller. Research and Development Manager"> <reports> <employee name="Diane L Margheim. Research and Development Engineer" /> <employee name="Gigi N Matthew. Research and Development Engineer" /> <employee name="Michael Raheem. Research and Development Manager" /> </reports> </employee> <employee name="Ovidiu V Cracium. Senior Tool Designer"> <reports> <employee name="Thierry B D'Hers. Tool Designer" /> <employee name="Ms. Janice M Galvin. Tool Designer" /> </reports> </employee> <employee name="Michael I Sullivan. Senior Design Engineer" /> <employee name="Sharon B Salavaria. Design Engineer" /> </reports> </employee> |
And finally, for the sake of completeness here is the HTML List version
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
IF Object_Id('dbo.HTMLHierarchicalListOfReports', 'FN') IS NOT null DROP FUNCTION dbo.HTMLHierarchicalListOfReports; GO /* create the function that creates an XML representation of the hierarchy */ CREATE FUNCTION HTMLHierarchicalListOfReports(@Me HIERARCHYID) RETURNS XML BEGIN RETURN (SELECT me.employee AS li, (SELECT dbo.HTMLHierarchicalListOfReports(reports.OrganizationNode) FROM staff AS reports WHERE reports.OrganizationNode.GetAncestor(1) = me.OrganizationNode FOR XML RAW(''), ROOT('ul'), ELEMENTS, TYPE ) AS li FROM staff AS me WHERE me.OrganizationNode = @Me FOR XML RAW(''), ELEMENTS, TYPE ); END; GO |
Which can be executed like this
|
1 2 3 4 |
SELECT Convert(VARCHAR(MAX),(SELECT dbo.HTMLHierarchicalListOfReports(OrganizationNode) FROM staff WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager' FOR XML RAW(''),ROOT('UL'),ELEMENTS)) |
…to give this HTML fragment …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<ul> <li>Roberto Tamburello. Engineering Manager</li> <li> <ul> <li>Rob Walters. Senior Tool Designer</li> <li>Ms. Gail A Erickson. Design Engineer</li> <li>Mr. Jossef H Goldberg. Design Engineer</li> <li>Dylan A Miller. Research and Development Manager</li> <ul> <li>Diane L Margheim. Research and Development Engineer</li> <li>Gigi N Matthew. Research and Development Engineer</li> <li>Michael Raheem. Research and Development Manager</li> </ul> </li> <li>Ovidiu V Cracium. Senior Tool Designer</li> <ul> <li>Thierry B D'Hers. Tool Designer</li> <li>Ms. Janice M Galvin. Tool Designer</li> </ul> </li> <li>Michael I Sullivan. Senior Design Engineer</li> <li>Sharon B Salavaria. Design Engineer</li> </ul> </li> </ul> |
So here we have it, all four commonly-used document types used for hierarchical lists, output from SQL Server. These are fairly simple to elaborate, and apologies in advance for any errors.
Load comments