1 CREATE FUNCTION [dbo].[parseJSON] (@Json NVARCHAR(MAX)) 2 RETURNS @hierarchy TABLE (element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ 3 sequenceNo [INT] NULL, /* the place in the sequence for the element */ 4 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 */ 5 Object_ID INT, /* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ 6 NAME NVARCHAR(2000), /* the name of the object */ 7 StringValue NVARCHAR(MAX) NOT NULL, /*the string representation of the value of the element. */ 8 ValueType VARCHAR(10) NOT NULL /* the declared type of the value represented as a string in StringValue*/ 9 ) 10 AS 11 BEGIN 12 13 DECLARE @FirstObject INT, --the index of the first open bracket found in the JSON string 14 @OpenDelimiter INT, --the index of the next open bracket found in the JSON string 15 @NextOpenDelimiter INT, --the index of subsequent open bracket found in the JSON string 16 @NextCloseDelimiter INT, --the index of subsequent close bracket found in the JSON string 17 @Type NVARCHAR(10), --whether it denotes an object or an array 18 @NextCloseDelimiterChar CHAR(1), --either a ‘}‘ or a ‘]‘ 19 @Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression 20 @Start INT, --index of the start of the token that you are parsing 21 @end INT, --index of the end of the token that you are parsing 22 @param INT, --the parameter at the end of the next Object/Array token 23 @EndOfName INT, --the index of the start of the parameter at end of Object/Array token 24 @token NVARCHAR(200), --either a string or object 25 @value NVARCHAR(MAX), -- the value as a string 26 @SequenceNo INT, -- the sequence number within a list 27 @name NVARCHAR(200), --the name as a string 28 @parent_ID INT, --the next parent ID to allocate 29 @lenJSON INT, --the current length of the JSON String 30 @characters NCHAR(36), --used to convert hex to decimal 31 @result BIGINT, --the value of the hex symbol being parsed 32 @index SMALLINT, --used for parsing the hex value 33 @Escape INT; --the index of the next escape character 34 DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are ‘escaped‘ in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string b 35 y tokens representing the string */ 36 (String_ID INT IDENTITY(1, 1), 37 StringValue NVARCHAR(MAX)); 38 SELECT --initialise the characters to convert hex to ascii 39 @characters = N‘0123456789abcdefghijklmnopqrstuvwxyz‘, 40 @SequenceNo = 0, --set the sequence no. to something sensible. 41 @parent_ID = 0; 42 WHILE 1 = 1 --forever until there is nothing more to do 43 BEGIN 44 SELECT @Start = PATINDEX(‘%[^a-zA-Z]["]%‘, @Json COLLATE SQL_Latin1_General_CP850_BIN); --next delimited string 45 IF @Start = 0 46 BREAK; --no more so drop through the WHILE loop 47 IF SUBSTRING(@Json, @Start + 1, 1) = ‘"‘ 48 BEGIN --Delimited Name 49 SET @Start = @Start + 1; 50 SET @end = PATINDEX(‘%[^\]["]%‘, RIGHT(@Json, LEN(@Json + ‘|‘) - @Start)); 51 END; 52 IF @end = 0 --no end delimiter to last string 53 BREAK; --no more 54 SELECT @token = SUBSTRING(@Json, @Start + 1, @end - 1); 55 SELECT @token = REPLACE(@token, substitutions.FromString, substitutions.ToString) 56 FROM ( SELECT ‘\"‘ AS FromString, 57 ‘"‘ AS ToString 58 UNION ALL 59 SELECT ‘\\‘, 60 ‘\‘ 61 UNION ALL 62 SELECT ‘\/‘, 63 ‘/‘ 64 UNION ALL 65 SELECT ‘\b‘, 66 CHAR(08) 67 UNION ALL 68 SELECT ‘\f‘, 69 CHAR(12) 70 UNION ALL 71 SELECT ‘\n‘, 72 CHAR(10) 73 UNION ALL 74 SELECT ‘\r‘, 75 CHAR(13) 76 UNION ALL 77 SELECT ‘\t‘, 78 CHAR(09) 79 UNION ALL 80 SELECT ‘゛‘, 81 CHAR(09)) substitutions; 82 SELECT @result = 0, 83 @Escape = 1; 84 WHILE @Escape > 0 85 BEGIN 86 SELECT @index = 0, 87 @Escape = PATINDEX(‘%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%‘, @token); 88 IF @Escape > 0 --if there is one 89 BEGIN 90 WHILE @index < 4 --there are always four digits to a \x sequence 91 BEGIN 92 SELECT --determine its value 93 @result 94 = @result + POWER(16, @index) 95 * (CHARINDEX(SUBSTRING(@token, @Escape + 2 + 3 - @index, 1), @characters) - 1), 96 @index = @index + 1; 97 END; 98 SELECT @token = STUFF(@token, @Escape, 6, NCHAR(@result)); 99 END; 100 END; 101 INSERT INTO @Strings (StringValue) 102 SELECT @token; 103 SELECT @Json = STUFF(@Json, @Start, @end + 1, ‘@string‘ + CONVERT(NVARCHAR(5), @@identity)); 104 END; 105 WHILE 1 = 1 --forever until there is nothing more to do 106 BEGIN 107 SELECT @parent_ID = @parent_ID + 1; 108 SELECT @FirstObject = PATINDEX(‘%[{[[]%‘, @Json COLLATE SQL_Latin1_General_CP850_BIN); --object or array 109 IF @FirstObject = 0 110 BREAK; 111 IF (SUBSTRING(@Json, @FirstObject, 1) = ‘{‘) 112 SELECT @NextCloseDelimiterChar = ‘}‘, 113 @Type = ‘object‘; 114 ELSE 115 SELECT @NextCloseDelimiterChar = ‘]‘, 116 @Type = ‘array‘; 117 SELECT @OpenDelimiter = @FirstObject; 118 WHILE 1 = 1 --find the innermost object or list... 119 BEGIN 120 SELECT @lenJSON = LEN(@Json + ‘|‘) - 1; 121 SELECT @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar, @Json, @OpenDelimiter + 1); 122 SELECT @NextOpenDelimiter 123 = PATINDEX(‘%[{[[]%‘, RIGHT(@Json, @lenJSON - @OpenDelimiter)COLLATE SQL_Latin1_General_CP850_BIN); --object 124 IF @NextOpenDelimiter = 0 125 BREAK; 126 SELECT @NextOpenDelimiter = @NextOpenDelimiter + @OpenDelimiter; 127 IF @NextCloseDelimiter < @NextOpenDelimiter 128 BREAK; 129 IF SUBSTRING(@Json, @NextOpenDelimiter, 1) = ‘{‘ 130 SELECT @NextCloseDelimiterChar = ‘}‘, 131 @Type = ‘object‘; 132 ELSE 133 SELECT @NextCloseDelimiterChar = ‘]‘, 134 @Type = ‘array‘; 135 SELECT @OpenDelimiter = @NextOpenDelimiter; 136 END; 137 SELECT @Contents = SUBSTRING(@Json, @OpenDelimiter + 1, @NextCloseDelimiter - @OpenDelimiter - 1); 138 SELECT @Json 139 = STUFF( 140 @Json, 141 @OpenDelimiter, 142 @NextCloseDelimiter - @OpenDelimiter + 1, 143 ‘@‘ + @Type + CONVERT(NVARCHAR(5), @parent_ID)); 144 WHILE (PATINDEX(‘%[A-Za-z0-9@+.e]%‘, @Contents COLLATE SQL_Latin1_General_CP850_BIN)) <> 0 145 BEGIN 146 IF @Type = ‘Object‘ --it will be a 0-n list containing a string followed by a string, number,boolean, or null 147 BEGIN 148 SELECT @SequenceNo = 0, 149 @end = CHARINDEX(‘:‘, ‘ ‘ + @Contents); --if there is anything, it will be a string-based name. 150 SELECT @Start = PATINDEX(‘%[^A-Za-z@][@]%‘, ‘ ‘ + @Contents); --AAAAAAAA 151 SELECT @token = SUBSTRING(‘ ‘ + @Contents, @Start + 1, @end - @Start - 1), 152 @EndOfName = PATINDEX(‘%[0-9]%‘, @token COLLATE SQL_Latin1_General_CP850_BIN), 153 @param = RIGHT(@token, LEN(@token) - @EndOfName + 1); 154 SELECT @token = LEFT(@token, @EndOfName - 1), 155 @Contents = RIGHT(‘ ‘ + @Contents, LEN(‘ ‘ + @Contents + ‘|‘) - @end - 1); 156 SELECT @name = StringValue 157 FROM @Strings 158 WHERE String_ID = @param; --fetch the name 159 END; 160 ELSE 161 SELECT @name = NULL, 162 @SequenceNo = @SequenceNo + 1; 163 SELECT @end = CHARINDEX(‘,‘, @Contents); -- a string-token, object-token, list-token, number,boolean, or null 164 IF @end = 0 165 SELECT @end = PATINDEX(‘%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%‘, @Contents + ‘ ‘) + 1; 166 SELECT @Start = PATINDEX(‘%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%‘, ‘ ‘ + @Contents); 167 --select @start,@end, LEN(@contents+‘|‘), @contents 168 SELECT @value = RTRIM(SUBSTRING(@Contents, @Start, @end - @Start)), 169 @Contents = RIGHT(@Contents + ‘ ‘, LEN(@Contents + ‘|‘) - @end); 170 IF SUBSTRING(@value, 1, 7) = ‘@object‘ 171 INSERT INTO @hierarchy (NAME, 172 sequenceNo, 173 parent_ID, 174 StringValue, 175 Object_ID, 176 ValueType) 177 SELECT @name, 178 @SequenceNo, 179 @parent_ID, 180 SUBSTRING(@value, 8, 5), 181 SUBSTRING(@value, 8, 5), 182 ‘object‘; 183 ELSE IF SUBSTRING(@value, 1, 6) = ‘@array‘ 184 INSERT INTO @hierarchy (NAME, 185 sequenceNo, 186 parent_ID, 187 StringValue, 188 Object_ID, 189 ValueType) 190 SELECT @name, 191 @SequenceNo, 192 @parent_ID, 193 SUBSTRING(@value, 7, 5), 194 SUBSTRING(@value, 7, 5), 195 ‘array‘; 196 ELSE IF SUBSTRING(@value, 1, 7) = ‘@string‘ 197 INSERT INTO @hierarchy (NAME, 198 sequenceNo, 199 parent_ID, 200 StringValue, 201 ValueType) 202 SELECT @name, 203 @SequenceNo, 204 @parent_ID, 205 StringValue, 206 ‘string‘ 207 FROM @Strings 208 WHERE String_ID = SUBSTRING(@value, 8, 5); 209 ELSE IF @value IN ( ‘true‘, ‘false‘ ) 210 INSERT INTO @hierarchy (NAME, 211 sequenceNo, 212 parent_ID, 213 StringValue, 214 ValueType) 215 SELECT @name, 216 @SequenceNo, 217 @parent_ID, 218 @value, 219 ‘boolean‘; 220 ELSE IF @value = ‘null‘ 221 INSERT INTO @hierarchy (NAME, 222 sequenceNo, 223 parent_ID, 224 StringValue, 225 ValueType) 226 SELECT @name, 227 @SequenceNo, 228 @parent_ID, 229 @value, 230 ‘null‘; 231 ELSE IF PATINDEX(‘%[^0-9]%‘, @value COLLATE SQL_Latin1_General_CP850_BIN) > 0 232 INSERT INTO @hierarchy (NAME, 233 sequenceNo, 234 parent_ID, 235 StringValue, 236 ValueType) 237 SELECT @name, 238 @SequenceNo, 239 @parent_ID, 240 @value, 241 ‘real‘; 242 ELSE 243 INSERT INTO @hierarchy (NAME, 244 sequenceNo, 245 parent_ID, 246 StringValue, 247 ValueType) 248 SELECT @name, 249 @SequenceNo, 250 @parent_ID, 251 @value, 252 ‘int‘; 253 254 IF @Contents = ‘ ‘ 255 SELECT @SequenceNo = 0; 256 END; 257 END; 258 RETURN; 259 END;