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;
浙公网安备 33010602011771号