Skip to content

Instantly share code, notes, and snippets.

@ChicBrother
ChicBrother / Hierarchy.sql
Created September 16, 2021 14:50 — forked from chilismaug/Hierarchy.sql
Phil Factor's Hiearchy table declaration for JSON werx
-- 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
ChicBrother / JSONEscaped.sql
Created September 16, 2021 14:50 — forked from chilismaug/JSONEscaped.sql
Phil Factor utility function to clean the json strings
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
ChicBrother / ToJSON.sql
Created September 16, 2021 14:49 — forked from chilismaug/ToJSON.sql
Phil Factor's SQL Table to JSON converter function
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
ChicBrother / JSONHierarchy_test.sql
Created September 16, 2021 14:49 — forked from chilismaug/JSONHierarchy_test.sql
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*/
)