通过SQL直接返回JSON

SQL语句：

查询返回字符串最大不能超过2033，用xml格式可以

1 DECLARE @jsonstr xml
2 SET @jsonstr =(sql查询语句 FOR JSON AUTO,INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER)
3 SELECT  @jsonstr

 sql实例：

DECLARE @jsonstr xml
SET @jsonstr =(
select t1.*,
(Select * from details1 details where t1.id = details.mainid for json path) as details,
(Select * from details2 details where t1.id = details.mainid for json path) as details
from table1 t1 
where t1.id > 0
FOR JSON AUTO,WITHOUT_ARRAY_WRAPPER)
SELECT  @jsonstr

 

posted @ 2023-01-10 11:16  lyran3  阅读(351)  评论(0)    收藏  举报