Table To JSON and back to Table TSQL MSSQL

TSQL

CREATE TABLE #TBL_ADM_USER
( user_id INT,
user_name NVARCHAR(1000)
)

INSERT INTO #TBL_ADM_USER
VALUES (1,’NAME1′)
INSERT INTO #TBL_ADM_USER
VALUES (2,’NAME2′)
INSERT INTO #TBL_ADM_USER
VALUES (3,’NAME3′)

DECLARE @XML_STR AS NVARCHAR(MAX)
SET @XML_STR = (SELECT user_id, user_name
FROM #TBL_ADM_USER
FOR XML PATH (‘row’))

SET @XML_STR = ‘
‘ + @XML_STR + ‘
‘;

DECLARE @cars xml;
SET @cars = @XML_STR

DECLARE @json AS NVARCHAR(MAX)
SET @json = (
SELECT Stuff( –we want to snip out the leading comma
(SELECT TheLine from –this is to glue each row into a string
(SELECT ‘,
{‘+ –this is the start of the row, representing the row object in the JSON list
–the local-name(.) is an eXPath function that gives you the name of the node
Stuff((SELECT ‘,”‘+coalesce(b.c.value(‘local-name(.)’, ‘NVARCHAR(255)’),”)+'”:”‘+
b.c.value(‘text()[1]’,’NVARCHAR(MAX)’) +'”‘
— ‘text()[1]’ gives you the text contained in the node
from x.a.nodes(‘*’) b(c) –get the row XML and split it into each node
for xml path(”),TYPE).value(‘(./text())[1]’,’NVARCHAR(MAX)’)
,1,1,”)+’}’–remove the first comma
from @Cars.nodes(‘/root/*’) x(a) –get every row
) JSON(theLine) –each row
for xml path(”),TYPE).value(‘.’,’NVARCHAR(MAX)’ )
,1,1,”)–remove the first leading comma
)

SELECT @json
SELECT * INTO #TBL_JSON FROM parseJSON(@json)

SELECT * FROM #TBL_JSON

DECLARE @SQL_STR AS NVARCHAR(MAX)
SET @SQL_STR = ‘
Select parent_ID,’

CREATE TABLE #TMP_COLUMN
(
ID INT IDENTITY(1,1),
COLUMN_NAME VARCHAR(100)
)

INSERT INTO #TMP_COLUMN (COLUMN_NAME)
SELECT DISTINCT NAME FROM #TBL_JSON
WHERE NAME ‘-‘

DECLARE @START_COLUMN_ID AS INT, @END_COLUMN_ID AS INT
SET @START_COLUMN_ID = 1
SET @END_COLUMN_ID = (SELECT MAX(ID) FROM #TMP_COLUMN)
DECLARE @TBL_COL_NAME AS NVARCHAR(1000)
WHILE @START_COLUMN_ID 0
BEGIN
SELECT @index=0,
–find the next hex escape sequence
@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 are always four digits to a \x sequence
BEGIN
SELECT –determine its value
@result=@result+POWER(16, @index)
*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
@characters)-1), @index=@index+1 ;

END
— and replace the hex sequence by its unicode value
SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))
END
END
–now store the string away
INSERT INTO @Strings (StringValue) SELECT @token
— and replace the string with a token
SELECT @JSON=STUFF(@json, @start, @end+1,
'@string'+CONVERT(NVARCHAR(5), @@identity))
END
— all strings are now removed. Now we find the first leaf.
WHILE 1=1 –forever until there is nothing more to do
BEGIN

SELECT @parent_ID=@parent_ID+1
–find the first object 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 @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@firstObject
WHILE 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-delimiter of either type
SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%',
RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)–object
IF @NextOpenDelimiter=0
BREAK
SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter
IF @NextCloseDelimiter<@NextOpenDelimiter
BREAK
IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@NextOpenDelimiter
END
—and parse out the list or name/value pairs
SELECT
@contents=SUBSTRING(@json, @OpenDelimiter+1,
@NextCloseDelimiter-@OpenDelimiter-1)
SELECT
@JSON=STUFF(@json, @OpenDelimiter,
@NextCloseDelimiter-@OpenDelimiter+1,
'@'+@type+CONVERT(NVARCHAR(5), @parent_ID))
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))0
BEGIN
IF @Type=’Object’ –it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT
@SequenceNo=0,@end=CHARINDEX(‘:’, ‘ ‘+@contents)–if there is anything, it will be a string-based name.
SELECT @start=PATINDEX(‘%[^A-Za-z@][@]%’, ‘ ‘+@contents collate SQL_Latin1_General_CP850_Bin)–AAAAAAAA
SELECT @token=SUBSTRING(‘ ‘+@contents, @start+1, @End-@Start-1),
@endofname=PATINDEX(‘%[0-9]%’, @token collate SQL_Latin1_General_CP850_Bin),
@param=RIGHT(@token, LEN(@token)-@endofname+1)
SELECT
@token=LEFT(@token, @endofname-1),
@Contents=RIGHT(‘ ‘+@contents, LEN(‘ ‘+@contents+’|’)-@end-1)
SELECT @name=stringvalue FROM @strings
WHERE string_id=@param –fetch the name
END
ELSE
SELECT @Name=null,@SequenceNo=@SequenceNo+1
SELECT
@end=CHARINDEX(‘,’, @contents)– a string-token, object-token, list-token, number,boolean, or null
IF @end=0
SELECT @end=PATINDEX(‘%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%’, @Contents+’ ‘ collate SQL_Latin1_General_CP850_Bin)
+1
SELECT
@start=PATINDEX(‘%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%’, ‘ ‘+@contents collate SQL_Latin1_General_CP850_Bin)
–select @start,@end, LEN(@contents+’|’), @contents
SELECT
@Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
@Contents=RIGHT(@contents+’ ‘, LEN(@contents+’|’)-@end)
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, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),
SUBSTRING(@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=SUBSTRING(@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 collate SQL_Latin1_General_CP850_Bin)>0
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘real’
ELSE
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘int’
if @Contents=’ ‘ Select @SequenceNo=0
END
END
INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT ‘-‘,1, NULL, ”, @parent_id-1, @type

RETURN
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s