实现sql Server 原生数据从XML生成JSON数据的实例代码
sql Server 是关系数据库，查询结果通常都是数据集，但是在一些特殊需求下，我们需要XML数据，最近这些年，JSON作为WebAPI常用的交换数据格式，那么数据库如何生成JSON数据呢？今天就写了一个DEMO.
1.创建表及测试数据
sql;">
SET NOCOUNT ON
IF OBJECT_ID('STATS') IS NOT NULL DROP TABLE STATS
IF OBJECT_ID('STATIONS') IS NOT NULL DROP TABLE STATIONS
IF OBJECT_ID('OPERATORS') IS NOT NULL DROP TABLE OPERATORS
IF OBJECT_ID('REVIEWS') IS NOT NULL DROP TABLE REVIEWS
-- Create and populate table with Station
CREATE TABLE STATIONS(ID INTEGER PRIMARY KEY,CITY NVARCHAR(20),STATE CHAR(2),LAT_N REAL,LONG_W REAL);
INSERT INTO STATIONS VALUES (13,'Phoenix','AZ',33,112);
INSERT INTO STATIONS VALUES (44,'Denver','CO',40,105);
INSERT INTO STATIONS VALUES (66,'Caribou','ME',47,68);
-- Create and populate table with Operators
CREATE TABLE OPERATORS(ID INTEGER PRIMARY KEY,NAME NVARCHAR(20),SURNAME NVARCHAR(20));
INSERT INTO OPERATORS VALUES (50,'John "The Fox"','Brown');
INSERT INTO OPERATORS VALUES (51,'Paul','Smith');
INSERT INTO OPERATORS VALUES (52,'Michael','Williams');
-- Create and populate table with normalized temperature and precipitation data
CREATE TABLE STATS (
STATION_ID INTEGER REFERENCES STATIONS(ID),MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12),TEMP_F REAL CHECK (TEMP_F BETWEEN -80 AND 150),RAIN_I REAL CHECK (RAIN_I BETWEEN 0 AND 100),PRIMARY KEY (STATION_ID,MONTH));
INSERT INTO STATS VALUES (13,1,57.4,0.31);
INSERT INTO STATS VALUES (13,7,91.7,5.15);
INSERT INTO STATS VALUES (44,27.3,0.18);
INSERT INTO STATS VALUES (44,74.8,2.11);
INSERT INTO STATS VALUES (66,6.7,2.10);
INSERT INTO STATS VALUES (66,65.8,4.52);
-- Create and populate table with Review
CREATE TABLE REVIEWS(STATION_ID INTEGER,STAT_MONTH INTEGER,OPERATOR_ID INTEGER)
insert into REVIEWS VALUES (13,50)
insert into REVIEWS VALUES (13,50)
insert into REVIEWS VALUES (44,51)
insert into REVIEWS VALUES (44,52)
insert into REVIEWS VALUES (44,50)
insert into REVIEWS VALUES (66,51)
insert into REVIEWS VALUES (66,51)
2.查询结果集
sql;">
select STATIONS.ID as ID,STATIONS.CITY as City,STATIONS.STATE as State,STATIONS.LAT_N as LatN,STATIONS.LONG_W as LongW,STATS.MONTH as Month,STATS.RAIN_I as Rain,STATS.TEMP_F as Temp,OPERATORS.NAME as Name,OPERATORS.SURNAME as Surname
from stations
inner join stats on stats.STATION_ID=STATIONS.ID
left join reviews on reviews.STATION_ID=stations.id
and reviews.STAT_MONTH=STATS.[MONTH]
left join OPERATORS on OPERATORS.ID=reviews.OPERATOR_ID
结果：
2.查询xml数据
sql;">
select stations.*,(select stats.*,(select OPERATORS.*
from OPERATORS
inner join reviews on OPERATORS.ID=reviews.OPERATOR_ID
where reviews.STATION_ID=STATS.STATION_ID
and reviews.STAT_MONTH=STATS.MONTH
for xml path('operator'),type
) operators
from STATS
where STATS.STATION_ID=stations.ID
for xml path('stat'),type
) stats
from stations
for xml path('station'),type
结果：
13
Phoenix
AZ
3.3000000e+001
1.1200000e+002
13
1
5.7400002e+001
3.1000000e-001
@H_502_57@
50
John "The Fox"
Brown
13
7
9.1699997e+001
5.1500001e+000
@H_502_57@
50
John "The Fox"
Brown
44
Denver
CO
4.0000000e+001
1.0500000e+002
44
1
2.7299999e+001
1.8000001e-001
44
7
7.4800003e+001
2.1099999e+000
@H_502_57@
51
Paul
Smith
@H_502_57@
52
Michael
Williams
@H_502_57@
50
John "The Fox"
Brown
66
Caribou
ME
4.7000000e+001
6.8000000e+001
66
1
6.6999998e+000
2.0999999e+000
@H_502_57@
51
Paul
Smith
66
7
6.5800003e+001
4.5200000e+000
@H_502_57@
51
Paul
Smith
3.如何生成JSON数据
1）创建辅助函数
sql;">
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)'),'')+'}'
from @XmlData.nodes('/*') x(a)
) JSON(theLine)
for xml path(''),TYPE).value('.','')+']'
return @m
END
<div class="jb51code">
<pre class="brush:sql;">
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
sql;">
select dbo.qfn_XmlToJson
(
(
select stations.ID,stations.CITY,stations.STATE,stations.LAT_N,stations.LONG_W,(select OPERATORS.*
from OPERATORS inner join reviews
on OPERATORS.ID=reviews.OPERATOR_ID
where reviews.STATION_ID=STATS.STATION_ID
and reviews.STAT_MONTH=STATS.MONTH
for xml path('operator'),type
) operators
from STATS
where STATS.STATION_ID=stations.ID for xml path('stat'),type
) stats
from stations for xml path('stations'),type
)
)
sql;">
[ {"ID":13,"CITY":"Phoenix","STATE":"AZ","LAT_N":3.3000000e+001,"LONG_W"
:1.1200000e+002,"stats":[ {"STATION_ID":13,"MONTH":1,"TEMP_F":5.7400002e+001,"
RAIN_I":3.1000000e-001,"operators":[ {"ID":50,"NAME":"John \"The Fox\"","SURNAME":"Brown"}]},{"STATION_ID":13,"MONTH":7,"TEMP_F":9.1699997e+001,"RAIN_I":5.1500001e+000,"operators":
[ {"ID":50,"SURNAME":"Brown"}]}]},{"ID":44,"CITY":"Denver","STATE":"CO","LAT_N":4.0000000e+001,"LONG_W":1.0500000e+002,"stats":[ {"STATION_ID":44,"TEMP_F":2.7299999e+001,"RAIN_I":1.8000001e-001},{"STATION_ID":44,"TEMP_F":7.4800003e+001,"RAIN_I":2.1099999e+000,"operators":[ {"ID":51,"NAME":"Paul","SURNAME":"Smith"},{"ID":52,"NAME":"Michael","SURNAME":"Williams"},{"ID":50,"NAME"
:"John \"The Fox\"",{"ID":66,"CITY":"Caribou","STATE":"ME","LAT_N":
4.7000000e+001,"LONG_W":6.8000000e+001,"stats":[ {"STATION_ID":66,"TEMP
_F":6.6999998e+000,"RAIN_I":2.0999999e+000,"
SURNAME":"Smith"}]},{"STATION_ID":66,"TEMP_F":6.5800003e+001,"RAIN_I":
4.5200000e+000,"SURNAME":"Smith"}]}]}]
