En esta publicación, demostraré cómo leer cadenas JSON y convertirlas a una tabla y convertir datos de una tabla a JSON y XML usando solo T-SQL. Este consejo es muy útil cuando utiliza una versión anterior a SQL Server 2016 y necesita leer cadenas Json.
JSON, acrónimo de "JavaScript Object Notation", es un formato ligero para intercambiar datos computacionales. La simplicidad de JSON ha resultado en su uso generalizado, especialmente como alternativa a XML en AJAX.
Una de las supuestas ventajas de JSON sobre XML como formato para intercambiar datos en este contexto es el hecho de que es mucho más fácil escribir un analizador JSON. En el propio JavaScript, JSON se puede analizar de forma trivial utilizando la función eval(). Esto fue importante para la aceptación de JSON dentro de la comunidad AJAX debido a la presencia de esta característica de JavaScript en todos los navegadores web actuales.
En la práctica, los argumentos sobre la facilidad de desarrollo y el rendimiento del analizador rara vez se pasan por alto debido a preocupaciones de seguridad en el uso de eval() y la creciente integración del procesamiento XML en los navegadores web modernos. Por esta razón, JSON se usa típicamente en entornos donde el tamaño del flujo de datos entre el cliente y el servidor es de suma importancia (de ahí su uso por parte de Google, Yahoo, etc., que atienden a millones de usuarios), donde se puede confiar explícitamente en la fuente de los datos y donde la pérdida de capacidades de procesamiento XSLT del lado del cliente para la manipulación de datos o la generación de interfaces no es una consideración.
Un gran uso del uso de JSON en SQL Server y lo que me motivó a escribir este post, es para la integración y consumo de datos de servicios web que utilizan JSON para establecer comunicación con el cliente que consume la información.
Leyendo una cadena JSON
Usando la función que publicaré a continuación, creada por factor phil, es posible leer cadenas JSON fácilmente, como se muestra en el siguiente ejemplo:
JSON1.1
Implementando la función: Ver código fuente
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
Creando una cadena JSON desde la base de datos
Con esta función, puede crear una cadena JSON a partir de una tabla en un formato específico. Para hacer esto, necesitamos crear este tipo de datos antes de usar nuestra función.
Requisito previo: creación de la función para procesar los datos:
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
Requisito previo: creación del 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 la función: Ver código fuente
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
Creando XML desde JSON
Usando la función fncJSON_To_XML, puede crear XML a partir de una cadena JSON pasada a la función, como en el siguiente ejemplo: 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
Solución alternativa para convertir una cadena JSON a XML
Con la siguiente función (escrita por Daniel Muchmacher), puede convertir fácilmente cadenas Json en un documento XML, donde puede usar SQL Server para navegar entre nodos y leer los datos según sus necesidades. Esta función es muy útil para instancias que utilizan una versión anterior a SQL Server 2016 (sin soporte JSON nativo), pero es necesario utilizar esta función.
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 fuente de la función
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
Eso es todo.
¡Hasta el próximo post!
Dirceu Resende
Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…