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

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.

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.

SQL Prompt is an add-in for SQL Server Management Studio (SSMS) and Visual Studio that strips away the repetition of coding. As well as offering advanced IntelliSense-style code completion, full formatting options, object renaming, and other productivity features, SQL Prompt also offers fast and comprehensive code analysis as you type.

Subscribe for more articles

Subscribe to our fortnightly newsletter

William Brewer is a SQL Server developer who has worked as a Database consultant and Business Analyst for several Financial Services organisations in the City of London. True to his name, he is also an expert on real ale.

Hi there,
You have an error in the last function, you missed the plural “s” – correctly (SELECT dbo.HTMLHierarchicalListOfReports(reports.OrganizationNode) instead of (SELECT dbo.HTMLHierarchicalListOfReport(reports.OrganizationNode)

Andrew Clarke

(ed) Now fixed: Thanks for letting us know. Our fault in setting the article, not William’s!

Boris Shimonov

Hi Andrew,

For XML you didn’t provide select statement.

Boris.

Andrew Clarke

— (ed)This worked for me!
SELECT dbo.xmlHierarchicalListOfReports(OrganizationNode)
FROM staff
WHERE Staff.employee LIKE ‘Roberto Tamburello. Engineering Manager’;
— I’ve added it to the text just in case,

Related articles

Whether or not to have NULLable columns in a table can be a religious debate, and how missing data is represented should be carefully considered during database design. In this article, Joe Celko considers the ways that SQL Server handles NULLs in several situations. … Read more

You are never too old to learn an important lesson. Like most programmers, I read programming language documentation for one of two reasons. 1. You have just found out about something new 2. You find out that you were wrong about something you thought you had read the first time, probably 10 years ago. Today’s … Read more

T-SQL window functions have been a fantastic addition to the T-SQL language. In this article, Kathi Kellenberger reviews how optimizations available in SQL Server 2019 can improve the performance of these functions.… Read more