Utilidades SQL - Storeds para framework OFFLINE

Creado por David Miralpeix, Modificado el Fri, 16 Feb 2024 a las 12:28 PM por David Miralpeix

Storeds necesarias en el modelo de datos para el generador de aplicaciones offline.


Código SQL:

--STOREDS APP OFFLINE
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 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*/
)

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 PrepareTable (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

¡Háganos saber cómo podemos mejorar este artículo!

Seleccione al menos una de las razones
Se requiere la verificación del CAPTCHA.

Sus comentarios se han enviado

Agradecemos su esfuerzo e intentaremos corregir el artículo