Archive

Posts Tagged ‘query’

Convert sql query result in to JSON

October 30, 2014 Leave a comment

DECLARE 
 @TableName NVARCHAR(512),
 @sql NVARCHAR(MAX),
 @xml XML;


SET @TableName = N'Customers';

SET @sql = N'SELECT @xml = CONVERT(NVARCHAR(MAX), (
 SELECT top 20 * FROM ' + @TableName + ' FOR XML path, root));';

EXEC sp_executesql @sql, N'@xml XML OUTPUT', @xml OUTPUT;

SELECT dbo.ToJSON (@xml)


USE [DB]
GO
/****** Object: UserDefinedFunction [dbo].[ToJSON] Script Date: 10/30/2014 19:24:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ToJSON] (@XMLResult XML)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @JSONVersion NVARCHAR(MAX), @Rowcount INT
SELECT @JSONVersion = '', @rowcount=COUNT(*) FROM @XMLResult.nodes('/root/*') x(a)
SELECT @JSONVersion=@JSONVersion+
STUFF(
 (SELECT TheLine FROM 
 (SELECT ',
 {'+
 STUFF((SELECT ',"'+COALESCE(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'":"'+
 REPLACE( --escape tab properly within a value
 REPLACE( --escape return properly
 REPLACE( --linefeed must be escaped
 REPLACE( --backslash too
 REPLACE(COALESCE(b.c.value('text()[1]','NVARCHAR(MAX)'),''),--forwardslash
 '\', '\\'), 
 '/', '\/'), 
 CHAR(10),'\n'), 
 CHAR(13),'\r'), 
 CHAR(09),'\t') 
 +'"' 
 FROM x.a.nodes('*') b(c) 
 FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')+'}'
 FROM @XMLResult.nodes('/root/*') x(a)
 ) JSON(theLine)
 FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,'')
IF @Rowcount>1 RETURN '['+@JSONVersion+'
]'
RETURN @JSONVersion
END

Advertisements
Categories: MS SQL Tags: , , ,