{"id":71858,"date":"2017-07-18T17:10:29","date_gmt":"2017-07-18T17:10:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=71858"},"modified":"2021-09-29T16:21:10","modified_gmt":"2021-09-29T16:21:10","slug":"71858","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/71858\/","title":{"rendered":"Representing a simple hierarchical list in SQL Server with JSON, YAML, XML and HTML"},"content":{"rendered":"

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 <\/a>to the point that I keep it as arms-length as possible.<\/p>\n

Because I would have found it useful myself, I\u2019ve 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.<\/p>\n

First, before we do anything else, we\u2019ll create some test data. In this example, I\u2019ll steal the employee hierarchy from AdventureWorks2014, and put it in a test table.<\/p>\n

--if necessary delete the existing table\r\nIF Object_Id('staff', 'U') IS NOT null DROP TABLE Staff;\r\nGO\r\n--create our staff hierarchy table\r\nCREATE TABLE Staff\r\n  (\r\n  OrganizationNode HIERARCHYID PRIMARY KEY,\r\n  employee VARCHAR(80)\r\n  );\r\n--steal the data from Adventureworks\r\nINSERT INTO Staff\r\n  (OrganizationNode, employee)\r\nSELECT \r\n    Employee.OrganizationNode, \r\n    Coalesce(Person.Title+' ','')+ Person.FirstName+' ' +Coalesce(Person.MiddleName+' ','')\r\n      + Person.LastName+Coalesce(' '+Person.Suffix,'')+ '. '+ Employee.JobTitle\r\n  FROM AdventureWorks2014.HumanResources.Employee Employee\r\n    INNER JOIN AdventureWorks2014.Person.Person person\r\n      ON Employee.BusinessEntityID = Person.BusinessEntityID\r\n  WHERE OrganizationNode IS NOT null;\r\nGO\r\n<\/pre>\n
First, we\u2019ll tackle a JSON rendering of the hierarchy<\/p>\n
IF Object_Id('dbo.JSONHierarchicalListOfReports', 'FN') IS NOT null\r\n  DROP FUNCTION dbo.JSONHierarchicalListOfReports;\r\nGO\r\n\/* create the function that creates a JSON representation of the hierarchy *\/\r\nCREATE FUNCTION JSONHierarchicalListOfReports(@Me HIERARCHYID)\r\nRETURNS VARCHAR(8000)\r\n  BEGIN\r\n    RETURN\r\n      (SELECT \r\n         Coalesce((SELECT '{\"'+\r\n\t\t    String_Escape(me.employee, 'json')+'\": ['+Stuff( --get a list of reports\r\n\t\t\t\t(SELECT ',' + dbo.JSONHierarchicalListOfReports(reports.OrganizationNode)\r\n\t\t\t\t\tFROM Staff AS reports\r\n\t\t\t\t\tWHERE reports.OrganizationNode.GetAncestor(1) = me.OrganizationNode\r\n\t\t\t\tFOR XML PATH(''), TYPE\r\n\t\t\t\t).value('.', 'varchar(max)'),1,\t1,'')+']}'\r\n         ),'\"'+String_Escape(me.employee, 'json')+'\"') AS reports\r\n         FROM Staff AS me\r\n\t\t  WHERE me.OrganizationNode = @Me\r\n      );\r\n  END;\r\nGO\r\n<\/pre>\n
We can now try it out \u2026<\/p>\n
SELECT dbo.JSONHierarchicalListOfReports(OrganizationNode)\r\n  FROM staff\r\n  WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager';\r\n<\/pre>\n
To get this JSON rendition (after prettifying to make it easier to read)<\/p>\n
{\r\n  \"Roberto Tamburello. Engineering Manager\": [\r\n    \"Rob Walters. Senior Tool Designer\",\r\n    \"Ms. Gail A Erickson. Design Engineer\",\r\n    \"Mr. Jossef H Goldberg. Design Engineer\",\r\n    {\r\n      \"Dylan A Miller. Research and Development Manager\": [\r\n        \"Diane L Margheim. Research and Development Engineer\",\r\n        \"Gigi N Matthew. Research and Development Engineer\",\r\n        \"Michael Raheem. Research and Development Manager\"\r\n      ]\r\n    },\r\n    {\r\n      \"Ovidiu V Cracium. Senior Tool Designer\": [\r\n        \"Thierry B D'Hers. Tool Designer\",\r\n        \"Ms. Janice M Galvin. Tool Designer\"\r\n      ]\r\n    },\r\n    \"Michael I Sullivan. Senior Design Engineer\",\r\n    \"Sharon B Salavaria. Design Engineer\"\r\n  ]\r\n}\r\n<\/pre>\n
So, emboldened, we try YAML. It turns out to be very easy as there is no support for it in SQL Server<\/p>\n
IF Object_Id('dbo.YAMLHierarchicalListOfReports', 'FN') IS NOT null\r\n  DROP FUNCTION dbo.YAMLHierarchicalListOfReports;\r\nGO\r\n\/* create the function that creates a YAML representation of the hierarchy *\/\r\nCREATE FUNCTION YAMLHierarchicalListOfReports(@Me HIERARCHYID, @indent INT =0)\r\nRETURNS VARCHAR(8000)\r\n  BEGIN\r\n    RETURN\r\n      (SELECT \r\n         Coalesce((SELECT \r\n\t\t    Space(@indent)+CASE WHEN @indent=0 THEN '' ELSE '- ' END\r\n\t\t\t+String_Escape(me.employee, 'json')+': '+Stuff( --get a list of reports\r\n\t\t\t\t(SELECT '\r\n' + dbo.YAMLHierarchicalListOfReports(reports.OrganizationNode,@indent+4)\r\n\t\t\t\t\tFROM Staff AS reports\r\n\t\t\t\t\tWHERE reports.OrganizationNode.GetAncestor(1) = me.OrganizationNode\r\n\t\t\t\tFOR XML PATH(''), TYPE\r\n\t\t\t\t).value('.', 'varchar(max)'),1,\t1,'')\r\n         ),space(@indent)+'- '+String_Escape(me.employee, 'json')) AS reports\r\n         FROM Staff AS me\r\n\t\t  WHERE me.OrganizationNode = @Me\r\n      );\r\n  END;\r\nGO\r\n <\/pre>\n
This will render the same manager and his reports even more simply, and doesn\u2019t need prettifying to understand<\/p>\n
SELECT '---\r\n' + dbo.YAMLHierarchicalListOfReports(OrganizationNode,DEFAULT)\r\n  FROM staff\r\n  WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager';\r\n<\/pre>\n
This, when executed gives the following YAML document \u2026<\/p>\n
--- \r\nRoberto Tamburello. Engineering Manager: \r\n    - Rob Walters. Senior Tool Designer\r\n    - Ms. Gail A Erickson. Design Engineer\r\n    - Mr. Jossef H Goldberg. Design Engineer\r\n    - Dylan A Miller. Research and Development Manager: \r\n        - Diane L Margheim. Research and Development Engineer\r\n        - Gigi N Matthew. Research and Development Engineer\r\n        - Michael Raheem. Research and Development Manager\r\n    - Ovidiu V Cracium. Senior Tool Designer: \r\n        - Thierry B D'Hers. Tool Designer\r\n        - Ms. Janice M Galvin. Tool Designer\r\n    - Michael I Sullivan. Senior Design Engineer\r\n    - Sharon B Salavaria. Design Engineer\r\n<\/pre>\n
The XML version is pretty simple …<\/p>\n
CREATE FUNCTION xmlHierarchicalListOfReports(@Me HIERARCHYID)\r\nRETURNS XML\r\n  BEGIN\r\n  RETURN\r\n      (SELECT me.employee AS \"@name\",\r\n        (SELECT dbo.xmlHierarchicalListOfReports(reports.OrganizationNode)\r\n           FROM Staff  AS reports\r\n           WHERE reports.OrganizationNode.GetAncestor(1) = me.OrganizationNode\r\n         FOR XML raw('') , ROOT('reports'), ELEMENTS, TYPE\r\n      )\r\n      FROM staff AS me\r\n      WHERE me.OrganizationNode = @Me\r\n      FOR XML path('employee')\r\n      );\r\n  END;\r\nGO\r\n<\/pre>\n
… and we can execute it like this …<\/p>\n
SELECT  dbo.xmlHierarchicalListOfReports(OrganizationNode)\r\n  FROM staff\r\n  WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager';\r\n<\/pre>\n
… to give the following XML (prettified to make it easier to read) \u2026<\/p>\n
<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<employee name=\"Roberto Tamburello. Engineering Manager\">\r\n   <reports>\r\n      <employee name=\"Rob Walters. Senior Tool Designer\" \/>\r\n      <employee name=\"Ms. Gail A Erickson. Design Engineer\" \/>\r\n      <employee name=\"Mr. Jossef H Goldberg. Design Engineer\" \/>\r\n      <employee name=\"Dylan A Miller. Research and Development Manager\">\r\n         <reports>\r\n            <employee name=\"Diane L Margheim. Research and Development Engineer\" \/>\r\n            <employee name=\"Gigi N Matthew. Research and Development Engineer\" \/>\r\n            <employee name=\"Michael Raheem. Research and Development Manager\" \/>\r\n         <\/reports>\r\n      <\/employee>\r\n      <employee name=\"Ovidiu V Cracium. Senior Tool Designer\">\r\n         <reports>\r\n            <employee name=\"Thierry B D'Hers. Tool Designer\" \/>\r\n            <employee name=\"Ms. Janice M Galvin. Tool Designer\" \/>\r\n         <\/reports>\r\n      <\/employee>\r\n      <employee name=\"Michael I Sullivan. Senior Design Engineer\" \/>\r\n      <employee name=\"Sharon B Salavaria. Design Engineer\" \/>\r\n   <\/reports>\r\n<\/employee><\/pre>\n
And finally, for the sake of completeness here is the HTML List version<\/p>\n
IF  Object_Id('dbo.HTMLHierarchicalListOfReports', 'FN') IS NOT null\r\n  DROP FUNCTION dbo.HTMLHierarchicalListOfReports;\r\nGO\r\n\/* create the function that creates an XML representation of the hierarchy *\/\r\nCREATE FUNCTION HTMLHierarchicalListOfReports(@Me HIERARCHYID)\r\nRETURNS XML\r\n  BEGIN\r\n  RETURN\r\n      (SELECT me.employee AS li,\r\n        (SELECT dbo.HTMLHierarchicalListOfReports(reports.OrganizationNode)\r\n           FROM staff AS reports\r\n           WHERE reports.OrganizationNode.GetAncestor(1) = me.OrganizationNode\r\n         FOR XML RAW(''), ROOT('ul'), ELEMENTS, TYPE\r\n      ) AS li\r\n      FROM staff AS me\r\n      WHERE me.OrganizationNode = @Me\r\n      FOR XML RAW(''), ELEMENTS, TYPE\r\n      );\r\n  END;\r\nGO\r\n<\/pre>\n
Which can be executed like this<\/p>\n
SELECT Convert(VARCHAR(MAX),(SELECT dbo.HTMLHierarchicalListOfReports(OrganizationNode)\r\n  FROM staff\r\n  WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager'\r\n  FOR XML RAW(''),ROOT('UL'),ELEMENTS))\r\n<\/pre>\n
\u2026to give this HTML fragment \u2026<\/p>\n
<ul>\r\n<li>Roberto Tamburello. Engineering Manager<\/li>\r\n<li>\r\n<ul>\r\n<li>Rob Walters. Senior Tool Designer<\/li>\r\n<li>Ms. Gail A Erickson. Design Engineer<\/li>\r\n<li>Mr. Jossef H Goldberg. Design Engineer<\/li>\r\n<li>Dylan A Miller. Research and Development Manager<\/li>\r\n<ul>\r\n<li>Diane L Margheim. Research and Development Engineer<\/li>\r\n<li>Gigi N Matthew. Research and Development Engineer<\/li>\r\n<li>Michael Raheem. Research and Development Manager<\/li>\r\n<\/ul>\r\n<\/li>\r\n<li>Ovidiu V Cracium. Senior Tool Designer<\/li>\r\n<ul>\r\n<li>Thierry B D'Hers. Tool Designer<\/li>\r\n<li>Ms. Janice M Galvin. Tool Designer<\/li>\r\n<\/ul>\r\n<\/li>\r\n<li>Michael I Sullivan. Senior Design Engineer<\/li>\r\n<li>Sharon B Salavaria. Design Engineer<\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ul>\r\n<\/pre>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
How difficult can it be to produce a simple hierarchical  list in JSON, YAML, XML and HTML from a SQL Server table that represents a simple hierarchy within an organisation. Well once you know, it is easy and William Brewer is on a mission to tell you how …<\/p>\n","protected":false},"author":213195,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143531],"tags":[5134],"coauthors":[6796],"class_list":["post-71858","post","type-post","status-publish","format-standard","hentry","category-blogs","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71858","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/213195"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71858"}],"version-history":[{"count":12,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71858\/revisions"}],"predecessor-version":[{"id":73566,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71858\/revisions\/73566"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71858"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71858"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71858"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71858"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}