ChicBrother

-- Create the data type  IF EXISTS (SELECT * FROM sys.types WHERE name LIKE 'Hierarchy')
  DROP TYPE dbo.Hierarchy
go

/****** Object:  UserDefinedTableType [dbo].[Hierarchy]    Script Date: 4/14/2020 8:48:14 AM ******/
CREATE TYPE [dbo].[Hierarchy] AS TABLE(
	[element_id] [int] NOT NULL, 		/* internal surrogate primary key gives the order of parsing and the list order */
	[sequenceNo] [int] NULL,		/* the place in the sequence for the element */
	[parent_ID] [int] NULL,			/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
	[Object_ID] [int] NULL,			/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */

ChicBrother

IF OBJECT_ID (N'dbo.JSONEscaped') IS NOT NULL     DROP FUNCTION dbo.JSONEscaped
GO
 
CREATE FUNCTION [dbo].[JSONEscaped] ( /* this is a simple utility function that takes a SQL String with all its clobber and outputs it as a sting with all the JSON escape sequences in it.*/
 @Unescaped NVARCHAR(MAX) --a string with maybe characters that will break json
 )
RETURNS NVARCHAR(MAX)
AS
BEGIN
  SELECT @Unescaped = REPLACE(@Unescaped, FROMString, TOString)

ChicBrother

	CREATE FUNCTION ToJSON
	(
	      @Hierarchy Hierarchy READONLY
	)
	 
	/*
	the function that takes a Hierarchy table and converts it to a JSON string
	 
	Author: Phil Factor
	Revision: 1.5

ChicBrother

create TABLE #hierarchy
          (
           element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
           parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
           Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
           NAME NVARCHAR(2000),/* the name of the object */
           StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
           ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
          )