Utilidades SQL - Storeds para framework OFFLINE

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