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.

First, we’ll tackle a JSON rendering of the hierarchy

We can now try it out …

To get this JSON rendition (after prettifying to make it easier to read)

So, emboldened, we try YAML. It turns out to be very easy as there is no support for it in SQL Server

This will render the same manager and his reports even more simply, and doesn’t need prettifying to understand

This, when executed gives the following YAML document …

The XML version is pretty simple …

… and we can execute it like this …

… to give the following XML (prettified to make it easier to read) …

And finally, for the sake of completeness here is the HTML List version

Which can be executed like this

…to give this HTML fragment …

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.