Neste post irei demonstrar como ler strings JSON e converter para uma tabela e converter os dados de uma tabela para JSON e XML utilizando apenas T-SQL. Essa dica é bem útil quando você está utilizando uma versão anterior ao SQL Server 2016 e precisa ler strings Json.
JSON, um acrônimo para “JavaScript Object Notation”, é um formato leve para intercâmbio de dados computacionais. A simplicidade de JSON tem resultado em seu uso difundido, especialmente como uma alternativa para XML em AJAX.
Uma das vantagens reivindicadas de JSON sobre XML como um formato para intercâmbio de dados neste contexto, é o fato de ser muito mais fácil escrever um analisador JSON. Em JavaScript mesmo, JSON pode ser analisado trivialmente usando a função eval(). Isto foi importante para a aceitação de JSON dentro da comunidade AJAX devido a presença deste recurso de JavaScript em todos os navegadores web atuais.
Na prática, os argumentos a respeito da facilidade de desenvolvimento e desempenho do analisador são raramente relevados devido aos interesses de segurança no uso de eval() e a crescente integração de processamento XML nos navegadores web modernos. Por esta razão JSON é tipicamente usado em ambientes onde o tamanho do fluxo de dados entre o cliente e o servidor é de supra importância (daí seu uso por Google, Yahoo, etc., os quais servem milhões de usuários), onde a fonte dos dados pode ser explicitamente confiável, e onde a perda dos recursos de processamento XSLT no lado cliente para manipulação de dados ou geração da interface, não é uma consideração.
Um ótimo uso para a utilização de JSON no SQL Server e o que me motivou a fazer este post, é para a integração e consumo de dados de webservices que utilizam JSON para estabelecer a comunicação com o cliente consumidor da informação.
Lendo uma string JSON
Com a utilização da função que vou postar abaixo, criada pelo Phil Factor, é possível ler strings JSON facilmente, conforme o exemplo abaixo:
JSON1.1
Implementando a função: Visualizar código-fonte
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
Criando uma string JSON a partir do banco
Como o uso dessa função, você pode criar uma string JSON a partir de uma tabela em um formato específico. Para isso, precisamos criar esse tipo de dado antes de utilizar a nossa função.
Pré-Requisito – Criando a função para tratar os dados:
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
Pré-Requisito – Criando o tipo JSON:
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)
)
Implementando a função: Visualizar código-fonte
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
Criando um XML a partir de um JSON
Com o uso da função fncJSON_To_XML, pode-se criar um XML a partir de uma string JSON passada para a função, assim com o exemplo abaixo: 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
Solução alternativa para converter uma string JSON para XML
Com a função abaixo (escrita pelo Daniel Muchmacher), você poderá facilmente converter strings Json para um documento XML, onde você poderá utilizar o SQL Server para navegador entre os nós e ler os dados conforme sua necessidade. Essa função é muito útil para instâncias que estão utilizando uma versão anterior ao SQL Server 2016 (sem suporte nativo a JSON), mas você precisa utilizar esse recurso.
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)
Resultado:
Código-fonte da função
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
É isso aí.
Até o próximo post!
Dirceu Resende
Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…