Storeds para el funcionamiento de la web api y las aplicaciones offline

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