Phil Factor's Hiearchy Table function example - for making JSON via XML with T-SQL before MSSS2016

JSONHierarchy_test.sql

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*/
          )
        
    
     ;With loc (Roworder,locationID, Name, CostRate, Availability, ModifiedDate)
        as
        (
        Select  ROW_NUMBER() OVER ( ORDER BY locationID) as RowOrder,
               LocationID, Name, CostRate, Availability, ModifiedDate
        from Adventureworks.production.location
        )
        
        INSERT INTO #Hierarchy (parent_ID,Object_ID,NAME,StringValue,ValueType)
                  Select Roworder,null,'LocationID', convert(varchar(5),LocationID),'int'  from loc
        union all Select Roworder,null,'Name', Name ,'string'  from  loc
        union all Select Roworder,null,'CostRate', convert(varchar(10),CostRate) ,'real'  from  loc
        union all Select Roworder,null,'Availability', convert(varchar(10),Availability) ,'real'  from  loc
        union all Select Roworder,null,'ModifiedDate', Convert(varchar(10),ModifiedDate,126) ,'string'  from  loc
        union all Select (Select count(*) from loc)+1, ROW_NUMBER() OVER ( ORDER BY locationID ), NULL,'1','object' from  loc
        union all Select null, (Select count(*) from loc)+1,'-','','array'