菜单
一、简单结果转Json
1.1创建存储过程
1、创建存储过程 SerializeJSON
CREATE PROCEDURE[dbo].[SerializeJSON](
@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString)'
SET @SQL = @SQL + 'AS('
SET @SQL = @SQL + @ParameterSQL+ 'FOR XML RAW,TYPE,ELEMENTS'
SET @SQL = @SQL + ')'
SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString=CAST(@XML AS VARCHAR(MAX))
DECLARE @JSON VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @KEY VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)
DECLARE @StartRoot VARCHAR(100);SET @StartRoot='<row>'
DECLARE @EndRoot VARCHAR(100);SET @EndRoot='</row>'
DECLARE @StartField VARCHAR(100);SET @StartField='<'
DECLARE @EndField VARCHAR(100);SET @EndField='>'
SET @RowStart=CharIndex(@StartRoot,@XMLString,0)
SET @JSON=''
WHILE @RowStart>0
BEGIN
SET @RowStart=@RowStart+Len(@StartRoot)
SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)
SET @Row=SubString(@XMLString,@RowStart,@RowEnd-@RowStart)
SET @JSON=@JSON+'{'
-- for each row
SET @FieldStart=CharIndex(@StartField,@Row,0)
WHILE @FieldStart>0
BEGIN
-- parse node key
SET @FieldStart=@FieldStart+Len(@StartField)
SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
SET @KEY=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
SET @JSON=@JSON+'"'+@KEY+'":'
-- parse node value
SET @FieldStart=@FieldEnd+1
SET @FieldEnd=CharIndex('</',@Row,@FieldStart)
SET @Value=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
SET @JSON=@JSON+'"'+@Value+'",'
SET @FieldStart=@FieldStart+Len(@StartField)
SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
SET @FieldStart=CharIndex(@StartField,@Row,@FieldEnd)
END
IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
SET @JSON=@JSON+'},'
--/ for each row
SET @RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
END
IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
SET @JSON='['+@JSON+']'
SELECT @JSON
END1.2 执行存储过程
执行存储过程：EXEC[SerializeJSON] 'SQL语句'二、带层级的Json格式
上述方式只能简易的Json,如何生成一个带层级的Json呢？
2.1 创建标量函数
1、首先创建两个函数
CREATE FUNCTION [dbo].[qfn_XmlToJson](@XmlData xml)
RETURNS nvarchar(max)
AS
BEGIN
declare @m nvarchar(max)
SELECT @m='['+Stuff
(
(SELECT theline from
(SELECT ','+' {'+Stuff
(
(SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'":'+
case when b.c.value('count(*)','int')=0
then dbo.[qfn_JsonEscape](b.c.value('text()[1]','NVARCHAR(MAX)'))
else dbo.qfn_XmlToJson(b.c.query('*'))
end
from x.a.nodes('*') b(c)
for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
,1,1,'')+'}'
from @XmlData.nodes('/*') x(a)
) JSON(theLine)
for xml path(''),TYPE).value('.','NVARCHAR(MAX)')
,1,1,'')+']'
return @m
END
CREATE FUNCTION [dbo].[qfn_JsonEscape](@value nvarchar(max) )
returns nvarchar(max)
as begin
if (@value is null) return 'null'
if (TRY_PARSE( @value as float) is not null) return @value
set @value=replace(@value,'\','\\')
set @value=replace(@value,'"','\"')
return '"'+@value+'"'
end
2.2 插入测试数据
SET NOCOUNT ON
IF OBJECT_ID('School') IS NOT NULL
DROP TABLE School
IF OBJECT_ID('FGroup') IS NOT NULL
DROP TABLE FGroup
IF OBJECT_ID('Class') IS NOT NULL
DROP TABLE Class
IF OBJECT_ID('Users') IS NOT NULL
DROP TABLE Users
-- Create and populate table with Station
CREATE TABLE School(ID INTEGER PRIMARY KEY, Name NVARCHAR(20), address CHAR(200));
INSERT INTO School VALUES (1, '佛山一中', '佛山市禅城区佛山一中');
INSERT INTO School VALUES (3, '佛山二中', '佛山市禅城区佛山二中');
INSERT INTO School VALUES (4, '佛山三中', '佛山市禅城区佛山三中');
-- Create and populate table with normalized temperature and precipitation data
CREATE TABLE FGroup (
School_ID INTEGER REFERENCES School(ID),
ID INTEGER PRIMARY KEY,
Name NVARCHAR(20));
INSERT INTO FGroup VALUES (1, 1, '初一');
INSERT INTO FGroup VALUES (1, 2, '初二');
INSERT INTO FGroup VALUES (1, 3, '初三');
INSERT INTO FGroup VALUES (3, 4,'初一');
INSERT INTO FGroup VALUES (3, 5, '初一');
INSERT INTO FGroup VALUES (4, 6, '初二');
-- Create and populate table with Operators
CREATE TABLE Class(
ID INTEGER PRIMARY KEY,
Group_ID INTEGER REFERENCES FGroup(ID),
NAME NVARCHAR(20));
INSERT INTO Class VALUES (1, 1, '一班');
INSERT INTO Class VALUES (2, 1, '二班');
INSERT INTO Class VALUES (3, 2, '一班');
INSERT INTO Class VALUES (4, 3, '三班');
INSERT INTO Class VALUES (5, 3, '四班');
INSERT INTO Class VALUES (6, 4, '一班');
INSERT INTO Class VALUES (7, 4, '二班');
INSERT INTO Class VALUES (8, 5, '一班');
INSERT INTO Class VALUES (9, 6, '一班');
-- Create and populate table with Review
CREATE TABLE Users(
ID INTEGER PRIMARY KEY,
Class_ID INTEGER REFERENCES Class(ID),
NAME NVARCHAR(20));
INSERT INTO Users VALUES (1, 1, '潘1班1');
INSERT INTO Users VALUES (2, 1, '潘1班2');
INSERT INTO Users VALUES (3, 2, '潘2班1');
INSERT INTO Users VALUES (4, 2, '潘2班2');
INSERT INTO Users VALUES (5, 3, '潘1班11');
INSERT INTO Users VALUES (6, 3, '潘1班12');
INSERT INTO Users VALUES (7, 4, '潘3班1');
INSERT INTO Users VALUES (8, 5, '潘4班1');
INSERT INTO Users VALUES (9, 6, '潘1班116');
INSERT INTO Users VALUES (10, 7, '潘2班117');
INSERT INTO Users VALUES (11, 8, '潘1班1118');
INSERT INTO Users VALUES (12, 9, '潘1班119');2.3 查询结果
select * from School s
left join FGroup g on s.ID=g.School_ID
left join Class c on g.ID=c.Group_ID
left join Users u on u.Class_ID=c.ID
2.4 生成多层级的Json，原理先转换成Xml.然后转换成Json
select dbo.qfn_XmlToJson(
(
select s.*,
(
select g.*,
(
select c.*,
(
select u.* from users u where u.Class_ID=c.ID for xml path('user'),type
) name,
(
select count(*) as UserNum from users u2 where u2.Class_ID=c.ID
) as UserNum
from Class c where c.Group_ID=g.ID for xml path('class'),type
) class from
FGroup g where g.School_ID=s.ID for xml path('group'),type
) Fgroup
from School s for xml path('shool'),type
)
)
[ {"ID":1,"Name":"佛山一中","address":"佛山市禅城区佛山一中 ","Fgroup":[ {"School_ID":1,"ID":1,"Name":"初一","class":[ {"ID":1,"Group_ID":1,"NAME":"一班","name":[ {"ID":1,"Class_ID":1,"NAME":"潘1班1"}, {"ID":2,"Class_ID":1,"NAME":"潘1班2"}],"UserNum":2}, {"ID":2,"Group_ID":1,"NAME":"二班","name":[ {"ID":3,"Class_ID":2,"NAME":"潘2班1"}, {"ID":4,"Class_ID":2,"NAME":"潘2班2"}],"UserNum":2}]}, {"School_ID":1,"ID":2,"Name":"初二","class":[ {"ID":3,"Group_ID":2,"NAME":"一班","name":[ {"ID":5,"Class_ID":3,"NAME":"潘1班11"}, {"ID":6,"Class_ID":3,"NAME":"潘1班12"}],"UserNum":2}]}, {"School_ID":1,"ID":3,"Name":"初三","class":[ {"ID":4,"Group_ID":3,"NAME":"三班","name":[ {"ID":7,"Class_ID":4,"NAME":"潘3班1"}],"UserNum":1}, {"ID":5,"Group_ID":3,"NAME":"四班","name":[ {"ID":8,"Class_ID":5,"NAME":"潘4班1"}],"UserNum":1}]}]}, {"ID":3,"Name":"佛山二中","address":"佛山市禅城区佛山二中 ","Fgroup":[ {"School_ID":3,"ID":4,"Name":"初一","class":[ {"ID":6,"Group_ID":4,"NAME":"一班","name":[ {"ID":9,"Class_ID":6,"NAME":"潘1班116"}],"UserNum":1}, {"ID":7,"Group_ID":4,"NAME":"二班","name":[ {"ID":10,"Class_ID":7,"NAME":"潘2班117"}],"UserNum":1}]}, {"School_ID":3,"ID":5,"Name":"初一","class":[ {"ID":8,"Group_ID":5,"NAME":"一班","name":[ {"ID":11,"Class_ID":8,"NAME":"潘1班1118"}],"UserNum":1}]}]}, {"ID":4,"Name":"佛山三中","address":"佛山市禅城区佛山三中 ","Fgroup":[ {"School_ID":4,"ID":6,"Name":"初二","class":[ {"ID":9,"Group_ID":6,"NAME":"一班","name":[ {"ID":12,"Class_ID":9,"NAME":"潘1班119"}],"UserNum":1}]}]}]