SQL to JSON as table

To create a method:

 /****** object:userdefinedfunction [dbo].    [Parsejson] Script DATE:2017/7/11 18:27:28 ******/set ansi_nulls ongoset quoted_identifier ongocreate FUNCTION [dbo]. [Parsejson] (@JSON NVARCHAR (MAX))  RETURNS @hierarchy TABLE (element_id INT IDENTITY (1, 1) 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,/* If the element has a parent, then it's in this column. The document is the ultimate parent and so can get the structure from recursing from the document */OBJECT_ID int,/* E Ach list or object has an object ID. This ties any elements to a parent. Lists is treated as objects here */name NVARCHAR (+),/* The name of the object */StringValue NVARCHAR (MAX) not NU Ll,/*the string representation of the value of the element.  */ValueType VARCHAR (+) NOT NULL/* The declared type of the value represented as a string in stringvalue*/) Asbegin DECLare @FirstObject int,--the index of the first open bracket found in the JSON string @OpenDelimiter int,--the index Of the next open bracket found in the JSON string @NextOpenDelimiter INT,--The index of subsequent open bracket found In the JSON string @NextCloseDelimiter INT,--The index of subsequent close bracket found in the JSON string @Type NV Archar,--Whether it denotes an object or an array @NextCloseDelimiterChar CHAR (1),--either a '} ' or a '] ' @Conte NTS NVARCHAR (MAX),--the unparsed contents of the bracketed expression @Start INT,--index of the Start of the token th At your is parsing @end int,--Index of the end of the token that is parsing @param INT,--The parameter at the E     nd of the next Object/array token @EndOfName INT,--The index of the start of the parameter at end of Object/array token  @token NVARCHAR,--either a string or object @value NVARCHAR (MAX),--the value as a string @SequenceNo int, --The sequence numberWithin a list @name NVARCHAR,--the name as a string @parent_ID INT,--the next parent ID to allocate @lenJSO N INT,--The current length of the JSON String @characters NCHAR ($),--used to convert hex to decimal @result BIGINT, --the value of the hex symbol being parsed @index SMALLINT,--used for parsing the hex value @Escape INT--the index  Of the next escape character DECLARE @Strings table/* In this temporary table we keep all Strings, even the names of The elements, since they is ' escaped ' in a different, and may contain, unescaped, brackets denoting objects or lists . These is replaced in the JSON string by tokens representing the string */(string_id INT IDENTITY (1, 1), Stri Ngvalue NVARCHAR (MAX)) select--initialise the characters to convert hex to ASCII @characters = ' 0123456789abcdefghij Klmnopqrstuvwxyz ', @SequenceNo =0,--set the sequence No.  to something sensible. /* Firstly we process all strings. This is the done because [{}and] aren ' t escaped in strings, which complicates an iterative parse.  */@parent_ID = 0; While 1=1--forever until there are nothing more to do BEGIN SELECT @start =patindex ('%[^a-za-z][']% ', @json Collate sql_latin1_general_cp850_bin)--next delimited string IF @start =0 break--no more so drop through the while L          OOP IF SUBSTRING (@json, @start +1, 1) = ' "' BEGIN--delimited Name SET @[email protected]+1; SET @end =patindex ('%[^\][']% ', right (@json, LEN (@json + ' | ') [email protected])        Collate sql_latin1_general_cp850_bin); End IF @end =0--no end delimiter to the last string break--no more SELECT @token =substring (@json, @start +1, @end-1)--now put in the escaped control characters SELECT @token =replace (@token, fromstring, TOString) FRO M (select ' \ ' ' as fromstring, ' ' as ToString union ALL select ' \ \ ', ' \ ' UNION ALL Select ' \/', '/' UNION all SELECT ' \b ', CHAR () union ALL SELECT ' \f ', char (+) union ALL select ' \ n ', char (TEN) union ALL select ' \ R ', char ( UNION all select ' \ t ', CHAR (in)) substitutions SELECT @result =0, @escape =1--begin to take out a NY hex Escape codes while @escape >0 BEGIN SELECT @index =0,--find the next hex escape seq          Uence @escape =patindex ('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]% ', @token collate sql_latin1_general_cp850_bin) IF @escape >0--if There is one BEGIN while @index <4--there be always four digits to a \x sequence BEGIN SELECT--determine its value @[email protecte                                D]+power (@index) * (CHARINDEX (SUBSTRING (@token, @[email protected], 1),                         @characters)-1), @[email protected]+1; END--and replace the hex sequence by its Unicode ValUE SELECT @token =stuff (@token, @escape, 6, NCHAR (@result)) end End--now Store the STR ing away INSERT into @Strings (stringvalue) Select @token--and replace the string with a token SELECT @J  Son=stuff (@json, @start, @end +1, ' @string ' +convert (NVARCHAR (5), @ @identity)) End-all strings is Now removed.    Now we find the first leaf. While 1=1--forever until there are nothing more to do BEGIN SELECT @[email protected]_id+1--find the first obje CT or list by looking for the open bracket SELECT @FirstObject =patindex ('%[{[[]% ', @json collate sql_latin1_general_cp850 _bin)--object or array if @FirstObject = 0 break if (SUBSTRING (@json, @FirstObject, 1) = ' {') SELECT @NextCloseDelimit Erchar= '} ', @type = ' object ' ELSE SELECT @NextCloseDelimiterChar = '] ', @type = ' array ' select @[email protected] WH    ILE 1=1--find The innermost object or list ... BEGIN SELECT @lenJSON =len (@JSON + ' | ') -1 --find the matching close-delimiter proceeding after the Open-delimiter SELECT @NextCloseDelimiter =charindex ( @NextCloseDelimiterChar, @json, @OpenDelimiter + 1)--is there an intervening Open-del Imiter of either type SELECT @NextOpenDelimiter =patindex ('%[{[[]% ', right (@json, @[email protected]) Collate sql_latin1_general_cp850_bin)--object IF @NextOpenDelimiter =0 break SELECT @[email protecte D][email protected] If @NextCloseDelimiter < @NextOpenDelimiter break if SUBSTRING (@json, @NextOpe Ndelimiter, 1) = ' {' SELECT @NextCloseDelimiterChar = '} ', @type = ' object ' ELSE select @NextCloseDelimiter    Char= '] ', @type = ' array ' SELECT @[email protected] END---and parse out the list or name/value pairs SELECT @contents =substring (@json, @OpenDelimiter +1, @[email protected]) SELECT @JSON =stuff (@js     On, @OpenDelimiter,           @[email protected]+1, ' @ ' [Email protected]+convert (NVARCHAR (5), @parent_ID)) while (P Atindex ('%[[email protected]+.e]% ', @contents collate sql_latin1_general_cp850_bin)) <>0 BEGIN IF @Type          = ' Object '--it is a 0-n list containing a string followed by a string, Number,boolean, or null BEGIN SELECT @SequenceNo =0, @end =charindex (': ', ' [email protected])--if There is anything, it'll be a string-          Based name. SELECT @start =patindex ('%[^[email protected]][@]% ', ' [email protected] collate sql_latin1_general_cp850            _bin)--aaaaaaaa SELECT @token =substring ("[email protected], @start +1, @[email protected]), @endofname =patindex ('%[0-9]% ', @token collate sql_latin1_general_cp850_bin), @param =right (@token, LEN (@token) [ email protected]+1) SELECT @token =left (@token, @endofname-1), @Contents =right ("[Emai L prOtected], LEN (' [email protected]+ ' | ')          [email protected])         SELECT @name =stringvalue from @strings WHERE [email protected]--fetch the name END ELSE Select @Name =null,@[email protected]+1 Select @end =charindex (', ', @contents)--a String-token, objec T-token, List-token, Number,boolean, or null IF @end =0 SELECT @end =patindex ('%[[email protected]+.e][^[ email protected]+.e]% ', @Contents + ' collate sql_latin1_general_cp850_bin) +1 SELECT @start =pa Tindex ('%[^[email protected]+.e][[email protected]+.e]% ', ' [email protected] collate SQL_Latin1_ General_cp850_bin)--select @start, @end, LEN (@contents + ' | '), @contents select @Value =rtrim (SUBSTRING (@c Ontents, @start, @[email protected]), @Contents =right (@contents + ", LEN (@contents + ' | ')      [email protected])    IF SUBSTRING (@value, 1, 7) = ' @object ' INSERT into @hierarchy      (NAME, Sequenceno, parent_id, StringValue, object_id, ValueType)         SELECT @name, @SequenceNo, @parent_ID, SUBSTRING (@value, 8, 5), SUBSTRING (@value, 8, 5), ' object ' ELSE IF SUBSTRING (@value, 1, 6) = ' @array ' INSERT into @hierarchy (NAME, Sequenceno, parent_id, String Value, object_id, ValueType) SELECT @name, @SequenceNo, @parent_ID, SUBSTRING (@value, 7, 5), SUBST               RING (@value, 7, 5), ' array ' ELSE IF SUBSTRING (@value, 1, 7) = ' @string ' INSERT into @hierarchy              (NAME, Sequenceno, parent_id, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, StringValue, ' string ' from @strings WHERE string_id=subst                RING (@value, 8, 5) ELSE IF @value in (' true ', ' false ') INSERT into @hierarchy                (NAME, Sequenceno, parent_id, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_id, @value, ' boolean ' ELSE IF @value = ' null ' INSERT into @hierarchy                  (NAME, Sequenceno, parent_id, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, ' null ' ELSE IF PATINDEX ('%[^0-9]% ', @value Coll Ate sql_latin1_general_cp850_bin) >0 INSERT into @hierarchy (NAME, Sequenceno, pare nt_id, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, ' real ' ELS                    E INSERT into @hierarchy (NAME, Sequenceno, parent_id, StringValue, ValueType) Select @name, @SequenceNo, @parent_ID, @value, ' int ' If @Contents = ' Select @SequenceNo =0 END endins ERT into @hierarchy (NAME, Sequenceno, parent_id, StringValue, object_id, ValueType) SELECT '-', 1, NULL, ', @parent_id-1 , @type--Returnendgo

Execute statement:

SELECT *     

　　

SQL to JSON as table