Skip to content

Instantly share code, notes, and snippets.

@ChicBrother
Forked from chilismaug/JSONHierarchy_test.sql
Created September 16, 2021 14:49
Show Gist options
  • Save ChicBrother/b522342ad6fee03be2edfd8c4ce6f422 to your computer and use it in GitHub Desktop.
Save ChicBrother/b522342ad6fee03be2edfd8c4ce6f422 to your computer and use it in GitHub Desktop.
Phil Factor's Hiearchy Table function example - for making JSON via XML with T-SQL before MSSS2016
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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment