/* *********************************************************************************************************************************************************************/
-- Drop the Assembly if already Exists 
/* *********************************************************************************************************************************************************************/

IF EXISTS
(
SELECT * FROM SYS.assemblies
WHERE Name = 'JSONBeautifier'
)
BEGIN
	
	DROP ASSEMBLY [JSONBeautifier]

END
GO

/* *********************************************************************************************************************************************************************/
-- Create the Assembly 
/* *********************************************************************************************************************************************************************/

CREATE ASSEMBLY [JSONBeautifier] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030082F390590000000000000000E00002210B010B00000C000000060000000000003E2A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000E42900005700000000400000C002000000000000000000000000000000000000006000000C000000AC2800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000440A000000200000000C000000020000000000000000000000000000200000602E72737263000000C00200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000202A000000000000480000000200050000230000AC0500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133001001300000001000011000228020000060A06730500000A0B2B00072A001B300300510200000200001100160A160B730600000A0C160D38200200000002096F0700000A13041104130911091F2C301711091F223B2401000011091F2C3B6501000038E501000011091F3A3BBE01000011091F5B5945030000001B000000C60100008900000011091F7B59450300000005000000B00100007300000038AB0100000811046F0800000A2607130A110A2D5900086F0900000A260016061758250A280A00000A6F0B00000A130B2B17110B6F0C00000A1305000872010000706F0D00000A2600110B6F0E00000A130A110A2DDCDE14110B14FE01130A110A2D08110B6F0F00000A00DC0000384801000007130A110A2D5900086F0900000A260016061759250A280A00000A6F0B00000A130B2B17110B6F0C00000A1305000872010000706F0D00000A2600110B6F0E00000A130A110A2DDCDE14110B14FE01130A110A2D08110B6F0F00000A00DC00000811046F0800000A2638DA0000000811046F0800000A261613060913072B07110616FE011306110716311302110717592513076F0700000A1F5CFE012B011600130A110A2DD91106130A110A2D050716FE010B38900000000811046F0800000A2607130A110A2D5500086F0900000A26001606280A00000A6F0B00000A130B2B17110B6F0C00000A1305000872010000706F0D00000A2600110B6F0E00000A130A110A2DDCDE14110B14FE01130A110A2D08110B6F0F00000A00DC00002B290811046F0800000A2607130A110A2D0C08720B0000706F0D00000A262B0B0811046F0800000A262B00000917580D09026F1000000AFE04130A110A3ACEFDFFFF086F1100000A13082B0011082A000000012800000200A20028CA00140000000002000701282F0114000000000200C40128EC0114000000001E02281200000A2A42534A4201000100000000000C00000076342E302E33303331390000000005006C000000E8010000237E0000540200006C02000023537472696E677300000000C00400001000000023555300D0040000100000002347554944000000E0040000CC00000023426C6F620000000000000002000001571D02080900000000FA253300160000010000000E000000020000000100000003000000020000001200000001000000040000000200000002000000010000000300000000000A0001000000000006003C0035000A0072005D000600B400A1000F00C80000000600F700D70006001701D7000A005001350106007101650106007F0135000E00BA01AE010600E001C5010600F401C50106002F021C020600440235000000000001000000000001000100010010001D000000050001000100518043000A0050200000000096007C0016000100702000000000960087001C000200F822000000008618920021000300000001009800000001009D00190092002500290092002B00310092002100390092002100110092003500410092002100490086014000410090014500410097014B005100EE0150000C0002026000140010026F0041009001740069003B027A00710050022100490058027E000900630282000900920021000E0004000D002000230030002E000B009A002E001300A3002E001B00AC003A0086005A0069000480000000000000000000000000000000001D00000004000000000000000000000001002C000000000004000000000000000000000001005100000000000400000000000000000000000100A201000000000000003C4D6F64756C653E004A534F4E426561757469666965722E646C6C004A534F4E42656175746966696572006D73636F726C69620053797374656D004F626A65637400494E44454E545F535452494E470053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E6700496E64656E744A534F4E00466F726D61744A736F6E002E63746F72004A534F4E007374720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E5465787400537472696E674275696C64657200537472696E67006765745F436861727300417070656E6400417070656E644C696E650053797374656D2E436F72650053797374656D2E4C696E7100456E756D657261626C650053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261626C6560310052616E67650049456E756D657261746F72603100476574456E756D657261746F72006765745F43757272656E740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F72004D6F76654E6578740049446973706F7361626C6500446973706F7365006765745F4C656E67746800546F537472696E670000092000200020002000000320000000C286D4310376AF41AABC8E0927E32A3A0008B77A5C561934E08902060E08200020002000200005000111090E0400010E0E0320000105200101111104200101080401000000042001010E0507020E11090420010308052001122103042000122109000215122D010808080515122D0108082000151231011300051512310108042000130005200112210E03200002032000080320000E13070C0802122108030802080E030215123101080801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000082F3905900000000020000001C010000C8280000C80A000052534453F5C2AB49B49E544E89ADCC4C92A4F20F04000000653A5C446576656C6F706D656E745C43235C4A534F4E426561757469666965725C4A534F4E426561757469666965725C6F626A5C44656275675C4A534F4E426561757469666965722E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C2A000000000000000000002E2A0000002000000000000000000000000000000000000000000000202A00000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000640200000000000000000000640234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004C4010000010053007400720069006E006700460069006C00650049006E0066006F000000A001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000048001300010049006E007400650072006E0061006C004E0061006D00650000004A0053004F004E0042006500610075007400690066006900650072002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005000130001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004A0053004F004E0042006500610075007400690066006900650072002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000403A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO

/* *********************************************************************************************************************************************************************/
-- Drop the Function if Already Exists
/* *********************************************************************************************************************************************************************/

IF EXISTS (SELECT * FROM SYS.ALL_OBJECTS WHERE NAME = 'IndentJSON' AND TYPE ='FS')
BEGIN
	DROP FUNCTION [dbo].[IndentJSON]
END 
GO

/* *********************************************************************************************************************************************************************/
-- Create the Function if Already Exists
/* *********************************************************************************************************************************************************************/
CREATE FUNCTION [dbo].[IndentJSON] (@JSON [nvarchar](MAX))
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [JSONBeautifier].[JSONBeautifier].[IndentJSON];
GO 

/* *********************************************************************************************************************************************************************/
-- Run SP_CONFIGURE to enable CLR
/* *********************************************************************************************************************************************************************/

EXEC SP_CONFIGURE 'clr enabled',1
GO

/* *********************************************************************************************************************************************************************/
-- Run RECONFIGURE to if above SP_CONFIGURE is run
/* *********************************************************************************************************************************************************************/
RECONFIGURE
GO

/* *********************************************************************************************************************************************************************/
-- Example 1 - With directly passing the JSON string
/* *********************************************************************************************************************************************************************/
SELECT [dbo].[IndentJSON]('{"id": 1,"name": "A green door","price": 12.50,"tags": ["home", "green"]}')
GO

/* *********************************************************************************************************************************************************************/
-- Example 2 - Query from a Table
/* *********************************************************************************************************************************************************************/
SELECT  [dbo].[IndentJSON](
		LTRIM
		(
		(
		SELECT TOP 2 A.ADDRESSID
					 ,A.CITY AS "REGION.CITY" 
					 ,A.COUNTRYREGION AS "REGION.COUNTRYREGION" 
		  FROM ADVENTUREWORKSLT2012.SALESLT.ADDRESS A
		 FOR JSON PATH , ROOT('Region')
		) 
		)
		)

GO

/* *********************************************************************************************************************************************************************/
-- Example 3 - Query from a Table to get the JSON into a variable and format it
/* *********************************************************************************************************************************************************************/

DECLARE @X NVARCHAR(max)

SELECT @X = 
(select top 2 a.AddressID
			 ,a.City as "Region.City" 
			 ,a.CountryRegion as "Region.CountryRegion" 
  from AdventureWorksLT2012.SalesLT.Address a
 for JSON path
)

SELECT [dbo].[IndentJSON](@X)

GO