如何从MS SQL 2014中的SQL查询制作JSON

Question

问题：从MS SQL 2014中的SQL查询生成JSON的最佳解决方案是什么？我创建了一个过程，但是它很慢。

我的例子：

DECLARE @customers xml;
DECLARE @json NVARCHAR(max);
SET @customers  =  (SELECT * FROM dbo.Customers FOR XML path, root)
EXEC [dbo].[HTTP_JSON]  @customers, @json

EXEC [dbo].[HTTP_JSON](@Shopping)

Create PROCEDURE [dbo].[HTTP_JSON]
@parameters xml, @response NVARCHAR(max) OUTPUT
WITH EXEC AS CALLER
AS
set @response = (SELECT Stuff(  
  (SELECT * from  
    (SELECT ',
    {'+  
      Stuff((SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+
                    b.c.value('text()[1]','NVARCHAR(MAX)') +'"'

             from x.a.nodes('*') b(c)  
             for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
        ,1,1,'')+'}' 
   from @parameters.nodes('/root/*') x(a)  
   ) JSON(theLine)  
  for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,''))
GO

Answer 1

只是为了好玩，我根据之前的答案创建了一个标量函数。

除了显而易见的XML参数外，我还添加了两个附加功能：1）包括Header（如下图所示），以及2）ToLower大小写（我更喜欢使用小写的JSON字段名称，该名称链接到类等）。

如果查询不止一个记录，则将返回格式化的数组。

Declare @Table table (ID int,Active bit,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @Table values
(1,1,'John','Smith','[e-mail]'),
(2,0,'Jane','Doe'  ,'[e-mail]')

Select A.ID
      ,A.Last_Name
      ,A.First_Name
      ,B.JSON
From  @Table A 
Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,1,(Select A.* For XML Raw)) ) B

退货

ID  Last_Name   First_Name  JSON
1   Smith       John        {"id":"1","active":"1","first_name":"John","last_name":"Smith","email":"[e-mail]"}
2   Doe         Jane        {"id":"2","active":"0","first_name":"Jane","last_name":"Doe","email":"[e-mail]"}

甚至更简单

Select JSON=[dbo].[udf-Str-JSON](0,1,(Select * From @Table for XML RAW))

标题打开时返回

{
    "status": {
        "successful": "true",
        "timestamp": "2016-10-09 06:08:16 GMT",
        "rows": "2"
    },
    "results": [{
        "id": "1",
        "active": "1",
        "first_name": "John",
        "last_name": "Smith",
        "email": "[e-mail]"
    }, {
        "id": "2",
        "active": "0",
        "first_name": "Jane",
        "last_name": "Doe",
        "email": "[e-mail]"
    }]
}

标题关闭时返回

[{
    "id": "1",
    "active": "1",
    "first_name": "John",
    "last_name": "Smith",
    "email": "[e-mail]"
}, {
    "id": "2",
    "active": "0",
    "first_name": "Jane",
    "last_name": "Doe",
    "email": "[e-mail]"
}]

UDF

ALTER FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)
Returns varchar(max)
AS
Begin
    Declare @Head varchar(max) = '',@JSON varchar(max) = ''
    ; with cteEAV as (Select RowNr=Row_Number() over (Order By (Select NULL))
                            ,Entity    = xRow.value('@*[1]','varchar(100)')
                            ,Attribute = xAtt.value('local-name(.)','varchar(100)')
                            ,Value     = xAtt.value('.','varchar(max)') 
                       From  @XML.nodes('/row') As R(xRow) 
                       Cross Apply R.xRow.nodes('./@*') As A(xAtt) )
          ,cteSum as (Select Records=count(Distinct Entity)
                            ,Head = IIF(@IncludeHead=0,IIF(count(Distinct Entity)<=1,'[getResults]','[[getResults]]'),Concat('{"status":{"successful":"true","timestamp":"',Format(GetUTCDate(),'yyyy-MM-dd hh:mm:ss '),'GMT','","rows":"',count(Distinct Entity),'"},"results":[[getResults]]}') ) 
                       From  cteEAV)
          ,cteBld as (Select *
                            ,NewRow=IIF(Lag(Entity,1)  over (Partition By Entity Order By (Select NULL))=Entity,'',',{')
                            ,EndRow=IIF(Lead(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,',','}')
                            ,JSON=Concat('"',IIF(@ToLowerCase=1,Lower(Attribute),Attribute),'":','"',Value,'"') 
                       From  cteEAV )
    Select @JSON = @JSON+NewRow+JSON+EndRow,@Head = Head From cteBld, cteSum
    Return Replace(@Head,'[getResults]',Stuff(@JSON,1,1,''))
End
-- Parameter 1: @IncludeHead 1/0
-- Parameter 2: @ToLowerCase 1/0 (converts field name to lowercase
-- Parameter 3: (Select * From ... for XML RAW)

**编辑-修正错字