使用SQLSERVER 查詢資料欄位,所查出來的資料,轉換為JSON格式,以方便VS建立類別使用

使用方式

select dbo.FlattenedJSON(
(
select top 1 * from 資料表
FOR XML path, root
)
)



而FlattenedJSON這個function 如下:

IF OBJECT_ID (N'dbo.FlattenedJSON') IS NOT NULL
DROP FUNCTION dbo.FlattenedJSON
GO
CREATE FUNCTION dbo.FlattenedJSON (@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
文章標籤
創作者介紹
創作者 Empty 的頭像
Empty

虎搞瞎搞

Empty 發表在 痞客邦 留言(0) 人氣()