This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 */ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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*/ | |
) | |