只是为了好玩，我根据之前的答案创建了一个标量函数。
除了显而易见的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','john.smith@email.com'),(2,0,'Jane','Doe' ,'jane.doe@email.com')Select A.ID ,A.Last_Name ,A.First_Name ,B.JSonFrom @Table A Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,1,(Select A.* For XML Raw)) ) B
退货
ID Last_Name First_Name JSON1 Smith John {"id":"1","active":"1","first_name":"John","last_name":"Smith","email":"john.smith@email.com"}2 Doe Jane {"id":"2","active":"0","first_name":"Jane","last_name":"Doe","email":"jane.doe@email.com"}甚至更简单
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": "john.smith@email.com" }, { "id": "2", "active": "0", "first_name": "Jane", "last_name": "Doe", "email": "jane.doe@email.com" }]}标题关闭时返回
[{ "id": "1", "active": "1", "first_name": "John", "last_name": "Smith", "email": "john.smith@email.com"}, { "id": "2", "active": "0", "first_name": "Jane", "last_name": "Doe", "email": "jane.doe@email.com"}]UDF
ALTER FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)Returns varchar(max)ASBegin 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)**编辑-修正错字
