Warning: preg_match(): Unknown modifier 'b' in /mnt/datadisk/www/src/Controllers/PostController.php on line 206 How to use JSON in SQL Server 2008, 2012 and 2014 - Reading JSON strings, importing into the database and exporting to XML — Dirceu ResendeSkip to content
In this post I will demonstrate how to read JSON strings and convert to a table and convert data from a table to JSON and XML using only T-SQL. This tip is very useful when you are using a version prior to SQL Server 2016 and need to read Json strings.
JSON, an acronym for “JavaScript Object Notation”, is a lightweight format for exchanging computational data. JSON's simplicity has resulted in its widespread use, especially as an alternative to XML in AJAX.
One of the claimed advantages of JSON over XML as a format for exchanging data in this context is the fact that it is much easier to write a JSON parser. In JavaScript itself, JSON can be trivially parsed using the eval() function. This was important for the acceptance of JSON within the AJAX community due to the presence of this JavaScript feature in all current web browsers.
In practice, arguments regarding ease of development and parser performance are rarely overlooked due to security concerns in using eval() and the increasing integration of XML processing in modern web browsers. For this reason JSON is typically used in environments where the size of the data stream between the client and server is of paramount importance (hence its use by Google, Yahoo, etc., which serve millions of users), where the source of the data can be explicitly trusted, and where the loss of client-side XSLT processing capabilities for data manipulation or interface generation is not a consideration.
A great use for using JSON in SQL Server and what motivated me to write this post, is for the integration and consumption of data from web services that use JSON to establish communication with the client consuming the information.
Reading a JSON string
Using the function I will post below, created by Phil Factor, it is possible to read JSON strings easily, as shown in the example below:
JSON1.1
Implementing the function: View source code
IF (OBJECT_ID (N'dbo.fncJSON_Read') IS NOT NULL) DROP FUNCTION dbo.fncJSON_Read
GO
CREATE FUNCTION dbo.fncJSON_Read (
@JSON NVARCHAR(MAX)
)
RETURNS @Retorno TABLE (
Id_Elemento INT NULL,
Nr_Sequencia [INT] NULL,
Id_Objeto_Pai INT,
Id_Objeto INT,
Ds_Nome NVARCHAR(2000),
Ds_String NVARCHAR(MAX) NOT NULL,
Ds_Tipo VARCHAR(10) NOT NULL
)
AS
BEGIN
DECLARE
@FirstObject INT,
@OpenDelimiter INT,
@NextOpenDelimiter INT,
@NextCloseDelimiter INT,
@Type NVARCHAR(10),
@NextCloseDelimiterChar CHAR(1),
@Contents NVARCHAR(MAX),
@Start INT,
@end INT,
@param INT,
@EndOfDs_Nome INT,
@token NVARCHAR(200),
@value NVARCHAR(MAX),
@Nr_Sequencia INT,
@Ds_Nome NVARCHAR(200),
@Id_Objeto_Pai INT,
@lenJSON INT,
@characters NCHAR(36),
@result BIGINT,
@index SMALLINT,
@Escape INT
DECLARE @Strings TABLE (
String_ID INT IDENTITY(1, 1),
Ds_String NVARCHAR(MAX)
)
SELECT
@characters = '0123456789abcdefghijklmnopqrstuvwxyz',
@Nr_Sequencia = 0,
@Id_Objeto_Pai = 0;
WHILE (1 = 1)
BEGIN
SELECT @Start = PATINDEX('%[^a-zA-Z]["]%', @JSON COLLATE SQL_Latin1_General_CP850_BIN);
IF (@Start = 0)
BREAK
IF (SUBSTRING(@JSON, @Start + 1, 1) = '"')
BEGIN
SET @Start = @Start + 1;
SET @end = PATINDEX('%[^\]["]%', RIGHT(@JSON, LEN(@JSON + '|') - @Start) COLLATE SQL_Latin1_General_CP850_BIN);
END
IF (@end = 0)
BREAK
SELECT
@token = SUBSTRING(@JSON, @Start + 1, @end - 1)
SELECT
@token = REPLACE(@token, FromString, ToString)
FROM (
SELECT '\"' AS FromString, '"' AS ToString
UNION ALL
SELECT '\\', '\'
UNION ALL
SELECT '\/', '/'
UNION ALL
SELECT '\b', CHAR(08)
UNION ALL
SELECT '\f', CHAR(12)
UNION ALL
SELECT '\n', CHAR(10)
UNION ALL
SELECT '\r', CHAR(13)
UNION ALL
SELECT '\t', CHAR(09)
) substitutions
SELECT
@result = 0,
@Escape = 1
WHILE (@Escape > 0)
BEGIN
SELECT
@index = 0,
@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)
BEGIN
WHILE (@index < 4)
BEGIN
SELECT
@result = @result + POWER(16, @index) * ( CHARINDEX(SUBSTRING(@token, @Escape + 2 + 3 - @index, 1), @characters) - 1 ),
@index = @index + 1;
END
SELECT @token = STUFF(@token, @Escape, 6, NCHAR(@result))
END
END
INSERT INTO @Strings ( Ds_String )
SELECT @token
SELECT @JSON = STUFF(@JSON, @Start, @end + 1, '@string' + CONVERT(NVARCHAR(5), @@IDENTITY))
END
WHILE (1 = 1)
BEGIN
SELECT @Id_Objeto_Pai = @Id_Objeto_Pai + 1
SELECT @FirstObject = PATINDEX('%[{[[]%', @JSON COLLATE SQL_Latin1_General_CP850_BIN)
IF (@FirstObject = 0)
BREAK
IF ( SUBSTRING(@JSON, @FirstObject, 1) = '{' )
SELECT @NextCloseDelimiterChar = '}', @Type = 'object'
ELSE
SELECT @NextCloseDelimiterChar = ']', @Type = 'array'
SELECT @OpenDelimiter = @FirstObject
WHILE (1 = 1)
BEGIN
SELECT @lenJSON = LEN(@JSON + '|') - 1
SELECT @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar, @JSON, @OpenDelimiter + 1)
SELECT @NextOpenDelimiter = PATINDEX('%[{[[]%', RIGHT(@JSON, @lenJSON - @OpenDelimiter) COLLATE SQL_Latin1_General_CP850_BIN)
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
SELECT @Contents = SUBSTRING(@JSON, @OpenDelimiter + 1, @NextCloseDelimiter - @OpenDelimiter - 1)
SELECT @JSON = STUFF(@JSON, @OpenDelimiter, @NextCloseDelimiter - @OpenDelimiter + 1, '@' + @Type + CONVERT(NVARCHAR(5), @Id_Objeto_Pai))
WHILE (( PATINDEX('%[A-Za-z0-9@+.e]%', @Contents COLLATE SQL_Latin1_General_CP850_BIN) ) <> 0)
BEGIN
IF (@Type = 'Object')
BEGIN
SELECT
@Nr_Sequencia = 0,
@end = CHARINDEX(':', ' ' + @Contents)
SELECT @Start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @Contents COLLATE SQL_Latin1_General_CP850_BIN)--AAAAAAAA
SELECT
@token = SUBSTRING(' ' + @Contents, @Start + 1, @end - @Start - 1),
@EndOfDs_Nome = PATINDEX('%[0-9]%', @token COLLATE SQL_Latin1_General_CP850_BIN),
@param = RIGHT(@token, LEN(@token) - @EndOfDs_Nome + 1)
SELECT
@token = LEFT(@token, @EndOfDs_Nome - 1),
@Contents = RIGHT(' ' + @Contents, LEN(' ' + @Contents + '|') - @end - 1)
SELECT
@Ds_Nome = Ds_String
FROM
@Strings
WHERE
String_ID = @param
END
ELSE
SELECT
@Ds_Nome = NULL,
@Nr_Sequencia = @Nr_Sequencia + 1
SELECT @end = CHARINDEX(',', @Contents)
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
@value = RTRIM(SUBSTRING(@Contents, @Start, @end - @Start)),
@Contents = RIGHT(@Contents + ' ', LEN(@Contents + '|') - @end)
IF (SUBSTRING(@value, 1, 7) = '@object')
BEGIN
INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Id_Objeto, Ds_Tipo )
SELECT
@Ds_Nome,
@Nr_Sequencia,
@Id_Objeto_Pai,
SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5),
'object'
END
ELSE BEGIN
IF (SUBSTRING(@value, 1, 6) = '@array')
INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Id_Objeto, Ds_Tipo )
SELECT
@Ds_Nome,
@Nr_Sequencia,
@Id_Objeto_Pai,
SUBSTRING(@value, 7, 5),
SUBSTRING(@value, 7, 5),
'array'
ELSE
IF (SUBSTRING(@value, 1, 7) = '@string')
INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )
SELECT
@Ds_Nome,
@Nr_Sequencia,
@Id_Objeto_Pai,
Ds_String,
'string'
FROM
@Strings
WHERE
String_ID = SUBSTRING(@value, 8, 5)
ELSE
IF (@value IN ( 'true', 'false' ))
INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )
SELECT
@Ds_Nome,
@Nr_Sequencia,
@Id_Objeto_Pai,
@value,
'boolean'
ELSE
IF (@value = 'null')
INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )
SELECT
@Ds_Nome,
@Nr_Sequencia,
@Id_Objeto_Pai,
@value,
'null'
ELSE
IF (PATINDEX('%[^0-9]%', @value COLLATE SQL_Latin1_General_CP850_BIN) > 0)
INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )
SELECT
@Ds_Nome,
@Nr_Sequencia,
@Id_Objeto_Pai,
@value,
'real'
ELSE
INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )
SELECT
@Ds_Nome,
@Nr_Sequencia,
@Id_Objeto_Pai,
@value,
'int'
IF (@Contents = ' ')
SELECT @Nr_Sequencia = 0
END
END
END
INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Id_Objeto, Ds_Tipo )
SELECT
'-',
1,
NULL,
'',
@Id_Objeto_Pai - 1,
@Type
DECLARE @Tabela_Final TABLE (
Id_Elemento INT IDENTITY(1, 1) NOT NULL,
Nr_Sequencia [INT] NULL,
Id_Objeto_Pai INT,
Id_Objeto INT,
Ds_Nome NVARCHAR(2000),
Ds_String NVARCHAR(MAX) NOT NULL,
Ds_Tipo VARCHAR(10) NOT NULL
)
INSERT INTO @Tabela_Final
SELECT
Nr_Sequencia,
Id_Objeto_Pai,
Id_Objeto,
Ds_Nome,
Ds_String,
Ds_Tipo
FROM
@Retorno
ORDER BY
ISNULL(Id_Objeto, Id_Objeto_Pai) DESC,
Id_Objeto_Pai DESC,
Id_Elemento
DELETE FROM @Retorno
INSERT INTO @Retorno
SELECT
Id_Elemento,
Nr_Sequencia,
Id_Objeto_Pai,
Id_Objeto,
Ds_Nome,
Ds_String,
Ds_Tipo
FROM
@Tabela_Final
RETURN
END
Creating a JSON string from the database
Using this function, you can create a JSON string from a table in a specific format. To do this, we need to create this type of data before using our function.
Prerequisite – Creating the function to process the data:
IF (OBJECT_ID (N'dbo.fncJSON_Escape') IS NOT NULL) DROP FUNCTION dbo.fncJSON_Escape
GO
CREATE FUNCTION dbo.fncJSON_Escape (
@Ds_String NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SELECT
@Ds_String = REPLACE(@Ds_String, FromString, ToString)
FROM
(
SELECT '\"' AS FromString, '"' AS ToString
UNION ALL
SELECT '\', '\\'
UNION ALL
SELECT '/', '\/'
UNION ALL
SELECT CHAR(08), '\b'
UNION ALL
SELECT CHAR(12), '\f'
UNION ALL
SELECT CHAR(10), '\n'
UNION ALL
SELECT CHAR(13), '\r'
UNION ALL
SELECT CHAR(09), '\t'
) substitutions
RETURN @Ds_String
END
Prerequisite – Creating the JSON type:
IF EXISTS (SELECT * FROM sys.types WHERE name = 'JSON') DROP TYPE dbo.JSON
CREATE TYPE dbo.JSON AS TABLE (
Id_Elemento INT NOT NULL,
Nr_Sequencia [INT] NULL,
Id_Objeto_Pai INT,
Id_Objeto INT,
Ds_Nome NVARCHAR(2000),
Ds_String NVARCHAR(MAX) NOT NULL,
Ds_Tipo VARCHAR(10) NOT NULL,
PRIMARY KEY (Id_Elemento)
)
Implementing the function: View source code
IF (OBJECT_ID (N'dbo.fncJSON_From_SQL') IS NOT NULL) DROP FUNCTION dbo.fncJSON_From_SQL
GO
CREATE FUNCTION dbo.fncJSON_From_SQL (
@Tabela_Original JSON READONLY
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE
@JSON NVARCHAR(MAX),
@NewJSON NVARCHAR(MAX),
@Where INT,
@ANumber INT,
@notNumber INT,
@indent INT,
@ii INT,
@CrLf CHAR(2)
SELECT
@CrLf = CHAR(13) + CHAR(10),
@JSON = CASE Ds_Tipo
WHEN 'array' THEN +COALESCE('{' + @CrLf + ' "' + Ds_Nome + '" : ', '') + '['
ELSE '{'
END + @CrLf + CASE WHEN Ds_Tipo = 'array' AND Ds_Nome IS NOT NULL THEN ' '
ELSE ''
END + '@Object' + CONVERT(VARCHAR(5), Id_Objeto) + @CrLf + CASE Ds_Tipo
WHEN 'array' THEN CASE WHEN Ds_Nome IS NULL THEN ']'
ELSE ' ]' + @CrLf + '}' + @CrLf
END
ELSE '}'
END
FROM
@Tabela_Original
WHERE
Id_Objeto_Pai IS NULL AND Ds_Tipo IN ( 'object', 'document', 'array' )
SELECT
@ii = 1000
WHILE (@ii > 0)
BEGIN
SELECT
@Where = PATINDEX('%[^[a-zA-Z0-9]@Object%', @JSON)
IF @Where = 0
BREAK
SET @indent = CHARINDEX(CHAR(10) + CHAR(13), REVERSE(LEFT(@JSON, @Where)) + CHAR(10) + CHAR(13)) - 1
SET @notNumber = PATINDEX('%[^0-9]%', RIGHT(@JSON, LEN(@JSON + '|') - @Where - 8) + ' ')
SET @NewJSON = NULL
SELECT
@NewJSON = COALESCE(@NewJSON + ',' + @CrLf + SPACE(@indent), '') + CASE WHEN parent.Ds_Tipo = 'array' THEN ''
ELSE COALESCE('"' + TheRow.Ds_Nome + '" : ', '')
END + CASE TheRow.Ds_Tipo
WHEN 'array' THEN ' [' + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), TheRow.[Id_Objeto]) + @CrLf + SPACE(@indent + 2) + ']'
WHEN 'object' THEN ' {' + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), TheRow.[Id_Objeto]) + @CrLf + SPACE(@indent + 2) + '}'
WHEN 'string' THEN '"' + dbo.fncJSON_Escape(TheRow.Ds_String) + '"'
ELSE TheRow.Ds_String
END
FROM
@Tabela_Original TheRow
INNER JOIN @Tabela_Original Parent ON parent.Id_Elemento = TheRow.Id_Objeto_Pai
WHERE
TheRow.Id_Objeto_Pai = SUBSTRING(@JSON, @Where + 8, @notNumber - 1)
SELECT
@JSON = STUFF(@JSON, @Where + 1, 8 + @notNumber - 1, @NewJSON),
@ii = @ii - 1
END
RETURN @JSON
END
GO
Creating XML from JSON
Using the function fncJSON_To_XML, you can create XML from a JSON string passed to the function, like the example below: JSON2
IF (OBJECT_ID(N'dbo.fncJSON_To_XML') IS NOT NULL) DROP FUNCTION dbo.fncJSON_To_XML
GO
CREATE FUNCTION dbo.fncJSON_To_XML (
@Tabela_Original JSON READONLY
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE
@XMLAsString NVARCHAR(MAX),
@NewXML NVARCHAR(MAX),
@Entities NVARCHAR(MAX),
@Objects NVARCHAR(MAX),
@Ds_Nome NVARCHAR(200),
@Where INT,
@ANumber INT,
@notNumber INT,
@indent INT,
@CrLf CHAR(2)
SELECT
@CrLf = CHAR(13) + CHAR(10),
@XMLAsString = '<?xml version="1.0" ?>
@Object' + CONVERT(VARCHAR(5), Id_Objeto) + '
'
FROM
@Tabela_Original
WHERE
Id_Objeto_Pai IS NULL AND Ds_Tipo IN ( 'object', 'array' )
WHILE (1 = 1)
BEGIN
SELECT
@Where = PATINDEX('%[^a-zA-Z0-9]@Object%', @XMLAsString)
IF (@Where = 0)
BREAK
SET @indent = CHARINDEX(CHAR(10) + CHAR(13), REVERSE(LEFT(@XMLAsString, @Where)) + CHAR(10) + CHAR(13)) - 1
SET @notNumber = PATINDEX('%[^0-9]%', RIGHT(@XMLAsString, LEN(@XMLAsString + '|') - @Where - 8) + ' ')
SET @Entities = NULL
SELECT
@Entities = COALESCE(@Entities + ' ', ' ') + Ds_Nome + '="' + REPLACE(REPLACE(REPLACE(Ds_String, '<', '<'), '&', '&'), '>', '>') + '"'
FROM
@Tabela_Original
WHERE
Id_Objeto_Pai = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1)
AND Ds_Tipo NOT IN ( 'array', 'object' )
SELECT
@Entities = COALESCE(@Entities, ''),
@Objects = '',
@Ds_Nome = CASE WHEN Ds_Nome = '-' THEN 'root'
ELSE Ds_Nome
END
FROM
@Tabela_Original
WHERE
[Id_Objeto] = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1)
SELECT
@Objects = @Objects + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), Id_Objeto)
FROM
@Tabela_Original
WHERE
Id_Objeto_Pai = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1)
AND Ds_Tipo IN ( 'array', 'object' )
IF (@Objects = '')
SELECT @NewXML = '<' + COALESCE(@Ds_Nome, 'item') + @Entities + ' />'
ELSE
SELECT @NewXML = '<' + COALESCE(@Ds_Nome, 'item') + @Entities + '>' + @Objects + @CrLf + +SPACE(@indent) + '</' + COALESCE(@Ds_Nome, 'item') + '>'
SELECT
@XMLAsString = STUFF(@XMLAsString, @Where + 1, 8 + @notNumber - 1, @NewXML)
END
RETURN @XMLAsString
END
Workaround to convert a JSON string to XML
With the function below (written by Daniel Muchmacher), you can easily convert Json strings to an XML document, where you can use SQL Server to browser between nodes and read the data as per your need. This function is very useful for instances that are using a version prior to SQL Server 2016 (without native JSON support), but you need to use this feature.
DECLARE @json VARCHAR(MAX) = '{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}'
SELECT dbo.fncJson2xml(@json)
Result:
Function source code
CREATE FUNCTION dbo.fncJson2xml (
@json VARCHAR(MAX)
)
RETURNS XML
AS
BEGIN
DECLARE
@output VARCHAR(MAX),
@key VARCHAR(MAX),
@value VARCHAR(MAX),
@recursion_counter INT,
@offset INT,
@nested BIT,
@array BIT,
@tab CHAR(1) = CHAR(9),
@cr CHAR(1) = CHAR(13),
@lf CHAR(1) = CHAR(10);
SET @json=LTRIM(RTRIM( REPLACE(REPLACE(REPLACE(@json, @cr, ''), @lf, ''), @tab, '')));
IF (LEFT(@json, 1)<>'{' OR RIGHT(@json, 1)<>'}')
RETURN '';
SET @json=LTRIM(RTRIM(SUBSTRING(@json, 2, LEN(@json)-2)));
SELECT @output = '';
WHILE (@json <> '')
BEGIN;
IF (LEFT(@json, 1)<>'"')
RETURN 'Expected quote (start of key name). Found "' + LEFT(@json, 1)+'"';
SET @key=SUBSTRING(@json, 2, PATINDEX('%[^\\]"%', SUBSTRING(@json, 2, LEN(@json))+' "'))
SET @json=LTRIM(SUBSTRING(@json, LEN(@key)+3, LEN(@json)));
IF (LEFT(@json, 1)<>':')
RETURN 'Expected ":" after key name, found "' + LEFT(@json, 1)+'"!';
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
IF (LEFT(@json, 1)='[')
SELECT @array=1, @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
IF (@array IS NULL)
SET @array=0;
WHILE (@array IS NOT NULL)
BEGIN
SELECT @value=NULL, @nested=0;
IF (@value IS NULL AND LEFT(@json, 1)='{')
BEGIN;
SELECT @recursion_counter=1, @offset=1;
WHILE (@recursion_counter<>0 AND @offset<LEN(@json))
BEGIN
SET @offset=@offset+ PATINDEX('%[{}]%', SUBSTRING(@json, @offset+1, LEN(@json)));
SET @recursion_counter=@recursion_counter + (CASE SUBSTRING(@json, @offset, 1) WHEN '{' THEN 1 WHEN '}' THEN -1 END);
END
SET @value=CAST( dbo.fncJson2xml(LEFT(@json, @offset)) AS varchar(max));
SET @json=SUBSTRING(@json, @offset+1, LEN(@json));
SET @nested=1;
END
IF (@value IS NULL AND LEFT(@json, 2)='""')
SELECT @value='', @json=LTRIM(SUBSTRING(@json, 3, LEN(@json)));
IF (@value IS NULL AND LEFT(@json, 1)='"')
BEGIN
SET @value=SUBSTRING(@json, 2, PATINDEX('%[^\\]"%', SUBSTRING(@json, 2, LEN(@json))+' "'));
SET @json=LTRIM(SUBSTRING(@json, LEN(@value)+3, LEN(@json)));
END
IF (@value IS NULL AND LEFT(@json, 1)=',')
SET @value='';
IF (@value IS NULL)
BEGIN
SET @value=LEFT(@json, PATINDEX('%[,}]%', REPLACE(@json, ']', '}')+'}')-1);
SET @json=SUBSTRING(@json, LEN(@value)+1, LEN(@json));
END;
SET @output = @output + @lf + @cr + REPLICATE(@tab, @@NESTLEVEL-1)+ '<' + REPLACE(@key, '@', '') + '>'+ ISNULL(REPLACE( REPLACE(@value, '\"', '"'), '\\', '\'), '')+ (CASE WHEN @nested=1 THEN @lf+@cr+REPLICATE(@tab, @@NESTLEVEL-1) ELSE '' END) + '</' + REPLACE(@key, '@', '') + '>'
IF (@array=0 AND @json <> '' AND LEFT(@json, 1) <> ',')
RETURN @output+'Expected "," after value, found "'+ LEFT(@json, 1)+'"!';
IF (@array=1 AND LEFT(@json, 1) NOT IN (',', ']'))
RETURN @output+'In array, expected "]" or "," after '+ 'value, found "'+LEFT(@json, 1)+'"!';
IF (@array=1 AND LEFT(@json, 1)=']')
BEGIN
SET @array=NULL;
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
IF (LEFT(@json, 1) NOT IN ('', ','))
BEGIN
RETURN 'Closed array, expected ","!';
END
END
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)+1));
IF (@array=0) SET @array=NULL;
END;
END;
RETURN CAST(@output AS XML);
END;
GO
That's it.
Until the next post!
Dirceu Resende
Database & BI Architect · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…