STOREDS APP OFFLINE
- Recordad que una vez pasado los scripts de creación de funciones y tipos de datos, deberás asegurarte de poner en los dos procedimiento (pNet_funParseJSON y pNet_offline_sync) las siguientes instrucciones:
Set Ansi_nulls ON y
Set Quoted_Identifier ON
- Si además necesitáis que la APP pueda realizar envíos muy grandes puede que os aparezca un error de “demasiados caracteres enviados”, solo si os aparece este mensaje podéis modificar el web.config de la WEBAPI:
<system.web>
<httpRuntime targetFramework="4.5" maxRequestLength="1048576" />
</system.web>
En system.webserver añadimos lo siguiente:
<system.webServer>
<security>
<requestFiltering>
<requestLimits maxAllowedContentLength="1073741824" />
</requestFiltering>
</security>
</system.webServer>
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[splitJSONstring]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[splitJSONstring]
GO
CREATE FUNCTION [dbo].[splitJSONstring] ( @stringToSplit NVARCHAR(MAX) )
RETURNS
@returnList TABLE ([id] int identity,[Name] [nvarchar] (max))
AS
BEGIN
DECLARE @name NVARCHAR(MAX)
DECLARE @pos INT
DECLARE @separator NVARCHAR(MAX)=N'},{'
WHILE CHARINDEX(@separator, @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(@separator, @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + 2, LEN(@stringToSplit)- @pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pNet_funParseJSON]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pNet_funParseJSON]
GO
CREATE PROCEDURE [dbo].[pNet_funParseJSON]( @JSON NVARCHAR(MAX))
/*
RETURNS @hierarchy table
(
element_id int IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
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 */
object_id int, /* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
name nvarchar(2000), /* the name of the object */
stringvalue nvarchar(max) NULL, /*the string representation of the value of the element. */
valuetype nvarchar(100) NOT null /* the declared type of the value represented as a string in stringvalue*/
)
*/
AS
BEGIN
DECLARE
@firstobject int, --the index of the first open bracket found in the JSON string
@opendelimiter int,--the index of the next open bracket found in the JSON string
@nextopendelimiter int,--the index of subsequent open bracket found in the JSON string
@nextclosedelimiter int,--the index of subsequent close bracket found in the JSON string
@type nvarchar(10),--whether it denotes an object or an array
@nextclosedelimiterChar CHAR(1),--either a '}' or a ']'
@contents nvarchar(MAX), --the unparsed contents of the bracketed expression
@start int, --index of the start of the token that you are parsing
@end int,--index of the end of the token that you are parsing
@param int,--the parameter at the end of the next Object/Array token
@endofname int,--the index of the start of the parameter at end of Object/Array token
@token nvarchar(max),--either a string or object
@value nvarchar(MAX), -- the value as a string
@name nvarchar(200), --the name as a string
@parent_id int,--the next parent ID to allocate
@lenjson int,--the current length of the JSON String
@characters NCHAR(62),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@escape int --the index of the next escape character
/* 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 by tokens representing the string
*/
if object_id('tempdb..#strings') is not null begin
DROP TABLE #strings
END
if object_id('tempdb..#hierarchy') is not null begin
DROP TABLE #hierarchy
END
if object_id('tempdb..#substitutions') is not null begin
DROP TABLE #substitutions
END
if object_id('tempdb..#splits') is not null begin
DROP TABLE #splits
END
create table #hierarchy
(
element_id int IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key give
s the order of parsing and the list order */
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 */
object_id int, /* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
name nvarchar(2000), /* the name of the object */
stringvalue nvarchar(max) NULL, /*the string representation of the value of the element. */
valuetype nvarchar(100) NOT null /* the declared type of the value represented as a string in stringvalue*/
)
CREATE TABLE #strings
(
string_id int IDENTITY(1, 1) PRIMARY KEY,
stringvalue nvarchar(MAX)
)
create table #substitutions (
from_string varchar(5) not null primary key,
to_string varchar(5) not null
)
create table #splits (
id int identity(1,1) primary key,
string nvarchar(max)
)
insert into #substitutions(from_string,to_string)
SELECT '\"' AS from_string, '"' AS to_string
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)
insert into #splits(string)
select name from dbo.splitJSONstring(@json)
order by id
--SELECT * FROM #SPLITS
declare @idstring int
declare @nextjson nvarchar(max)=N''
while exists(
select 1 from #splits
) begin
SELECT TOP 1
@JSON = STRING,
@IDSTRING = ID,
@firstobject =null,
@opendelimiter = null,
@nextopendelimiter = null,
@nextclosedelimiter = null,
@type = null,
@nextclosedelimiterChar =null,
@contents =null,
@start =null,
@end =null,
@param =null,
@endofname =null,
@token =null,
@value =null,
@name =null,
@parent_id =null,
@lenjson =null,
@characters=null,
@result =null,
@index =null,
@escape =null
FROM #splits ORDER BY Id
--print cast(@IDSTRING as varchar(10))
/* initialise the characters to convert hex to ascii */
SELECT
@characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
@parent_id = 0;
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
WHILE 1 = 1 /* forever until there is nothing more to do */
BEGIN
--print cast(@start as varchar(10))
SELECT @start = PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin); /* next delimited string */
IF @start = 0 BREAK /*no more so drop through the WHILE loop */
IF SUBSTRING(@json, @start+1, 1) = '"'
BEGIN /* Delimited name */
SET @start = @start+1;
SET @end = PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);
END
IF @end = 0 /*no end delimiter to last string*/
BREAK /* no more */
SELECT @token = SUBSTRING(@json, @start+1, @end-1)
/* now put in the escaped control characters */
SELECT @token = REPLACE(@token, from_string, to_string)
FROM #substitutions
/*
(
SELECT '\"' AS from_string, '"' AS to_string
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
/*Begin to take out any hex escape codes*/
WHILE @escape > 0
BEGIN
/* find the next hex escape sequence */
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 /* if there is one */
BEGIN
WHILE @index < 4 /* there are always four digits to a \x sequence */
BEGIN
/* determine its value */
SELECT
@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
SELECT @NEXTJSON = @NEXTJSON + @JSON + N','
DELETE FROM #splits where id = @idstring
end
SET @JSON = @NEXTJSON
--SELECT @JSON,LEN(@JSON)
--PRINT 'SEGUNDO BUCLE'
/* 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 /* WHILE PATINDEX */
IF @type = 'object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/
BEGIN
SELECT @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
BEGIN
SELECT @name = null
END
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, parent_id, stringvalue, object_id, valuetype)
SELECT @name, @parent_id, SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5), 'object'
ELSE
IF SUBSTRING(@value, 1, 6) = '@array'
INSERT INTO #hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT @name, @parent_id, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array'
ELSE
IF SUBSTRING(@value, 1, 7) = '@string'
INSERT INTO #hierarchy (name, parent_id, stringvalue, valuetype)
SELECT @name, @parent_id, stringvalue, 'string'
FROM #strings
WHERE string_id = SUBSTRING(@value, 8, 5)
ELSE
IF @value IN ('true', 'false')
INSERT INTO #hierarchy (name, parent_id, stringvalue, valuetype)
SELECT @name, @parent_id, @value, 'boolean'
ELSE
IF @value = 'null'
INSERT INTO #hierarchy (name, parent_id, stringvalue, valuetype)
SELECT @name, @parent_id, null, 'null'
ELSE
IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin) > 0
INSERT INTO #hierarchy (name, parent_id, stringvalue, valuetype)
SELECT @name, @parent_id, @value, 'real'
ELSE
INSERT INTO #hierarchy (name, parent_id, stringvalue, valuetype)
SELECT @name, @parent_id, @value, 'int'
END /* WHILE PATINDEX */
END /* WHILE 1=1 forever until there is nothing more to do */
INSERT INTO #hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT '-', NULL, '', @parent_id - 1, @type
select * from #hierarchy
END
GOif exists (select * from sys.tables where name = 'Net_Offline_Sync') begin drop table Net_Offline_Sync end go /****** Object: Table [dbo].[Net_Offline_Sync] Script Date: 22/02/2016 9:42:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Net_Offline_Sync]( [IdSync] [int] IDENTITY(1,1) NOT NULL, [IdApp] [nvarchar](50) NOT NULL, [FechaSync] [smalldatetime] NOT NULL CONSTRAINT [DF_Net_Offline_Sync_FechaSync] DEFAULT (getdate()), [Usuario] [nvarchar](150) NOT NULL, [JsonValue] [nvarchar](max) NOT NULL, [Finalizado] [bit] NOT NULL CONSTRAINT [DF_Net_Offline_Sync_Finalizado] DEFAULT ((0)), [Error] [bit] NOT NULL CONSTRAINT [DF_Net_Offline_Sync_Error] DEFAULT ((0)), [ErrorDesc] [nvarchar](max) NULL, [SyncGUID] [uniqueidentifier] NOT NULL, [IdEmpleado] [int] NOT NULL CONSTRAINT [DF_Net_Offline_Sync_IdEmpleado] DEFAULT ((0)), CONSTRAINT [PK_Net_Offline_Sync] PRIMARY KEY CLUSTERED ( [IdSync] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
if exists (select * from sys.types where name = 'JSONHierarchy' ) begin drop type JSONHierarchy end go /****** Object: UserDefinedTableType [dbo].[JSONHierarchy] Script Date: 22/02/2016 9:45:11 ******/ CREATE TYPE [dbo].[JSONHierarchy] AS TABLE( [element_id] [int] NOT NULL, [parent_ID] [int] NULL, [Object_ID] [int] NULL, [NAME] [nvarchar](2000) NULL, [StringValue] [nvarchar](max) NULL, [ValueType] [varchar](10) NOT NULL ) GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pNet_offline_sync]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pNet_offline_sync]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[pNet_offline_sync]
@SyncGUID nvarchar(max),
@StoredRet nvarchar(150)
as
BEGIN
declare @Json nvarchar(max)
select @Json='{'+jsonvalue+'}' from net_offline_sync where convert(nvarchar(max),syncGUID)=@SyncGUID
DECLARE @TabPropiedades JSONHierarchy;
insert into @TabPropiedades
--select * from dbo.funParseJson(@Json)
exec pNet_funParseJSON @json
declare @Fields as nvarchar(max)
declare @Ids as nvarchar(max)
declare @name as nvarchar(max)
declare CursorPivot CURSOR for
SELECT
name,
'Select top 1 @FieldsRET=left(S,len(s)-1) from (SELECT parent_id,replace(replace(convert(varchar(max),(SELECT convert(varchar(max),name)
FROM @TabPropiedades A WHERE A.parent_id = B.parent_id FOR XML PATH(''name''), TYPE)),''<name>'',''''),''</name>'','','') S
FROM @TabPropiedades B where parent_id in (select object_id from @TabPropiedades where parent_id ='+convert(varchar,object_id)+')) Oper' as Fields,
'Select distinct @IdsRET=left(S,len(s)-1) from(
SELECT replace(replace(convert(varchar(max),(SELECT convert(varchar(max),parent_id)
FROM (select distinct parent_id from @TabPropiedades A where parent_id in (select object_id from @TabPropiedades where parent_id ='+convert(varchar,object_id)+') ) X
FOR XML PATH(''parent_id''), TYPE)),''<parent_id>'',''''),''</parent_id>'','','') S
) Oper' as Ids
FROM @TabPropiedades where parent_id =(select object_id FROM @TabPropiedades where parent_id is null)
OPEN CursorPivot
FETCH NEXT FROM CursorPivot INTO @name,@Fields, @Ids
declare @SqlPivot as nvarchar(max)
set @sqlpivot=''
DECLARE @ParmDefinition nvarchar(500);
WHILE @@FETCH_STATUS = 0 BEGIN
declare @FieldsRet as nvarchar(max)
declare @IdsRET as nvarchar(max)
SET @ParmDefinition = N'@TabPropiedades JSONHierarchy readonly, @FieldsRet varchar(max) OUTPUT';
EXECUTE sp_executesql @fields, @ParmDefinition, @tabpropiedades = @tabpropiedades, @FieldsRet=@FieldsRet OUTPUT;
SET @ParmDefinition = N'@TabPropiedades JSONHierarchy readonly, @IdsRET varchar(max) OUTPUT';
EXECUTE sp_executesql @Ids, @ParmDefinition, @tabpropiedades = @tabpropiedades, @IdsRET=@IdsRET OUTPUT;
set @SqlPivot=@SqlPivot+'
SELECT parent_ID,'+@FieldsRET+'
INTO #'+@name+'
FROM
(SELECT parent_ID,name,stringValue
FROM @TabPropiedades where parent_ID in ('+@IdsRET+') ) p
PIVOT
(
MAX (stringValue)
FOR NAME IN ('+@FieldsRET+')
) AS pvt
ORDER BY parent_ID;'
FETCH NEXT FROM CursorPivot INTO @name,@Fields, @Ids
END
CLOSE CursorPivot
DEALLOCATE CursorPivot
set @SqlPivot = @SqlPivot +';exec '+@StoredRet+''''+@SyncGUID+''''
SET @ParmDefinition = N'@TabPropiedades JSONHierarchy readonly';
EXECUTE sp_executesql @SqlPivot, @ParmDefinition, @tabpropiedades = @tabpropiedades;
return -1
END
GO--esta stored va en ambas BBDD
if exists (select * from sys.procedures where name = 'CRM_GetJSON') begin
drop procedure [CRM_GetJSON]
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CRM_GetJSON] (
@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH Pr
epareTable (XMLString) '
SET @SQL = @SQL + 'AS ( '
SET @SQL = @SQL + @ParameterSQL+ ' FOR XML PATH(''row''), ROOT(''table'') , ELEMENTS XSINIL '
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString = CAST(@XML AS VARCHAR(MAX))
SET @XMLString=REPLACE(@XMLString,'xsi:nil="True"','')
DECLARE @JSON VARCHAR(MAX)
DECLARE @JSONROW VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @KEY VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)
DECLARE @StartRoot VARCHAR(100); SET @StartRoot = '<row>'
DECLARE @EndRoot VARCHAR(100); SET @EndRoot = '</row>'
DECLARE @StartField VARCHAR(100); SET @StartField = '<'
DECLARE @EndField VARCHAR(100); SET @EndField = '>'
DECLARE @TabRows TABLE(Valor varchar(MAX))
SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)
SET @JSON = ''
WHILE @RowStart > 0
BEGIN
SET @RowStart = @RowStart+Len(@StartRoot)
SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)
SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)
SET @JSON = @JSON+'{'
SET @JSONROW=''
-- for each row
SET @FieldStart = CharIndex(@StartField, @Row, 0)
WHILE @FieldStart > 0
BEGIN
-- parse node key
SET @FieldStart = @FieldStart+Len(@StartField)
SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
SET @KEY = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
IF RIGHT(@KEY,1)='/' BEGIN --Valor NULL
SET @KEY=LEFT(@KEY,LEN(@KEY)-1)
SET @JSONROW= @JSONROW+'"'+@KEY+'":null,'
END ELSE BEGIN
-- parse node value
SET @FieldStart = @FieldEnd+1
SET @FieldEnd = CharIndex('</', @Row, @FieldStart)
IF LOWER(@KEY) LIKE 'imagen%' BEGIN
SET @Value = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
END ELSE BEGIN
SET @Value =REPLACE(Replace(Replace(Replace(Replace(SubString(@Row, @FieldStart, @FieldEnd-@FieldStart), '\', '\\'), '"', '\"'), Char(13), '\n') ,Char(10),''),Char(9),' ')
END
SET @JSONROW= @JSONROW+'"'+@KEY+'":' +'"'+@Value+'",'
END
SET @FieldStart = @FieldStart+Len(@StartField)
SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)
END
IF LEN(@JSONROW)>0
SET @JSONROW =',{'+ SubString(@JSONROW, 0, LEN(@JSONROW))+'}'
INSERT INTO @TabRows(Valor)
VALUES(@JSONROW)
--/ for each row
SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)
END
select '[' + STUFF((
select
Valor
from @TabRows
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') + ']' Json
END
GO¿Le ha sido útil este artículo?
¡Qué bien!
Gracias por sus comentarios
¡Sentimos mucho no haber sido de ayuda!
Gracias por sus comentarios
Sus comentarios se han enviado
Agradecemos su esfuerzo e intentaremos corregir el artículo