Contents
How to consume hierarchical JSON documents in SQL Server?
It looks less elegant than the recursive version but runs a five times the speed. a seventy row JSON document is translated into a hierarchy table in 25 ms on my very slow test server! Parent_ID INT, /* if the element has a parent then it is in this column.
Is it possible to consume JSON in SQL Server?
JSON isn’t the easiest of document formats for transferring tabular data, but it is popular and you are likely to need to use it. This used to be a big problem in SQL Server because there wasn’t a native method for either creating or consuming JSON documents until SQL Server 2016.
Can a JSON table be returned in a SQL query?
Return data from a SQL Server table formatted as JSON. If you have a web service that takes data from the database layer and returns it in JSON format, or if you have JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query.
How to flatten hierarchy in a JSON document?
JSON documents may have sub-elements and hierarchical data that cannot be directly mapped into the standard relational columns. In this case, you can flatten JSON hierarchy by joining parent entity with sub-arrays. In the following example, the second object in the array has sub-array representing person skills.
How to convert a hierarchical table into XML?
Converting the data from its Hierarchical table form will be different for each application, but is easy with a CTE. You can, alternatively, convert the hierarchical table into XML and interrogate that with XQuery parent_ID INT,/* if the element has a parent then it is in this column.
How to show hierarchical list in SQL Server?
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?
How to consume JSON strings in SQL Server?
I wrote a succession of articles, Consuming JSON Strings in SQL Server, Producing JSON Documents From SQL Server Queries via TSQL and SQL Server JSON to Table and Table to JSON that illustrated ways of doing it, slow and quirky though they were.