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 GO
IF 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 GO
if 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