create PROCEDURE [dbo].[usp_SerializeJSON]
@ParameterSQL as varchar(max)
AS
BEGIN
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 RAW,TYPE,ELEMENTS'
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))
declare @JSON 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 = ''
declare @EndRoot varchar(100);
set @EndRoot = ''
declare @StartField varchar(100);
set @StartField = '
declare @EndField varchar(100);
set @EndField = '>'
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 + '{'
-- 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)
set @JSON = @JSON + '"' + @KEY + '":'
-- parse node value
set @FieldStart = @FieldEnd + 1
set @FieldEnd = charindex('', @Row, @FieldStart)
set @Value = substring(@Row, @FieldStart,@FieldEnd - @FieldStart)
set @JSON = @JSON + '"' + @Value + '",'
set @FieldStart = @FieldStart + len(@StartField)
set @FieldEnd = charindex(@EndField, @Row, @FieldStart)
set @FieldStart = charindex(@StartField, @Row,@FieldEnd)
end
if len(@JSON) > 0 set @JSON = substring(@JSON, 0, len(@JSON))
set @JSON = @JSON + '},'
--/ for each row
set @RowStart = charindex(@StartRoot, @XMLString, @RowEnd)
end
if len(@JSON) > 0 set @JSON = substring(@JSON, 0, len(@JSON))
set @JSON = '[' + @JSON + ']'
select @JSON
END