實現SQL Server 原生數據從XML生成JSON數據的實例代碼
SQL Server 是關系數據庫，查詢結果通常都是數據集，但是在一些特殊需求下，我們需要XML數據，最近這些年，JSON作為WebAPI常用的交換數據格式，那么數據庫如何生成JSON數據呢？今天就寫了一個DEMO.
1.創建表及測試數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
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, 1, 27.3, 0.18); INSERT INTO STATS VALUES (44, 7, 74.8, 2.11); INSERT INTO STATS VALUES (66, 1, 6.7, 2.10); INSERT INTO STATS VALUES (66, 7, 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,1,50) insert into REVIEWS VALUES (13,7,50) insert into REVIEWS VALUES (44,7,51) insert into REVIEWS VALUES (44,7,52) insert into REVIEWS VALUES (44,7,50) insert into REVIEWS VALUES (66,1,51) insert into REVIEWS VALUES (66,7,51) |
2.查詢結果集
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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 |
結果：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
<station> <ID>13</ID> <CITY>Phoenix</CITY> <STATE>AZ</STATE> <LAT_N>3.3000000e+001</LAT_N> <LONG_W>1.1200000e+002</LONG_W> <stats> <stat> <STATION_ID>13</STATION_ID> <MONTH>1</MONTH> <TEMP_F>5.7400002e+001</TEMP_F> <RAIN_I>3.1000000e-001</RAIN_I> <operators> <operator> <ID>50</ID> <NAME>John "The Fox"</NAME> <SURNAME>Brown</SURNAME> </operator> </operators> </stat> <stat> <STATION_ID>13</STATION_ID> <MONTH>7</MONTH> <TEMP_F>9.1699997e+001</TEMP_F> <RAIN_I>5.1500001e+000</RAIN_I> <operators> <operator> <ID>50</ID> <NAME>John "The Fox"</NAME> <SURNAME>Brown</SURNAME> </operator> </operators> </stat> </stats> </station> <station> <ID>44</ID> <CITY>Denver</CITY> <STATE>CO</STATE> <LAT_N>4.0000000e+001</LAT_N> <LONG_W>1.0500000e+002</LONG_W> <stats> <stat> <STATION_ID>44</STATION_ID> <MONTH>1</MONTH> <TEMP_F>2.7299999e+001</TEMP_F> <RAIN_I>1.8000001e-001</RAIN_I> </stat> <stat> <STATION_ID>44</STATION_ID> <MONTH>7</MONTH> <TEMP_F>7.4800003e+001</TEMP_F> <RAIN_I>2.1099999e+000</RAIN_I> <operators> <operator> <ID>51</ID> <NAME>Paul</NAME> <SURNAME>Smith</SURNAME> </operator> <operator> <ID>52</ID> <NAME>Michael</NAME> <SURNAME>Williams</SURNAME> </operator> <operator> <ID>50</ID> <NAME>John "The Fox"</NAME> <SURNAME>Brown</SURNAME> </operator> </operators> </stat> </stats> </station> <station> <ID>66</ID> <CITY>Caribou</CITY> <STATE>ME</STATE> <LAT_N>4.7000000e+001</LAT_N> <LONG_W>6.8000000e+001</LONG_W> <stats> <stat> <STATION_ID>66</STATION_ID> <MONTH>1</MONTH> <TEMP_F>6.6999998e+000</TEMP_F> <RAIN_I>2.0999999e+000</RAIN_I> <operators> <operator> <ID>51</ID> <NAME>Paul</NAME> <SURNAME>Smith</SURNAME> </operator> </operators> </stat> <stat> <STATION_ID>66</STATION_ID> <MONTH>7</MONTH> <TEMP_F>6.5800003e+001</TEMP_F> <RAIN_I>4.5200000e+000</RAIN_I> <operators> <operator> <ID>51</ID> <NAME>Paul</NAME> <SURNAME>Smith</SURNAME> </operator> </operators> </stat> </stats> </station> |
3.如何生成JSON數據
1）創建輔助函數
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
CREATE FUNCTION [dbo].[qfn_XmlToJson](@XmlData xml) RETURNS nvarchar(max) ASBEGIN 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 |
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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 |
3）查詢sql
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
select dbo.qfn_XmlToJson ( ( select stations.ID,stations.CITY,stations.STATE,stations.LAT_N,stations.LONG_W , (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('stations'),type ) ) |
結果：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
[ {"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,"NAME":"John \"The Fox\"","SURNAME":"Brown"}]}]}, {"ID":44,"CITY":"Denver","STATE":"CO","LAT_N":4.0000000e+001,"LONG_W":1.0500000e+002,"stats":[ {"STATION_ID":44,"MONTH":1,"TEMP_F":2.7299999e+001,"RAIN_I":1.8000001e-001}, {"STATION_ID":44,"MONTH":7,"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\"","SURNAME":"Brown"}]}]}, {"ID":66,"CITY":"Caribou","STATE":"ME","LAT_N":4.7000000e+001,"LONG_W":6.8000000e+001,"stats":[ {"STATION_ID":66,"MONTH":1,"TEMP_F":6.6999998e+000,"RAIN_I":2.0999999e+000,"operators":[ {"ID":51,"NAME":"Paul","SURNAME":"Smith"}]}, {"STATION_ID":66,"MONTH":7,"TEMP_F":6.5800003e+001,"RAIN_I":4.5200000e+000,"operators":[ {"ID":51,"NAME":"Paul","SURNAME":"Smith"}]}]}] |
總結：
JSON作為靈活的Web通信交換架構，如果把配置數據存放在數據庫中，直接獲取JSON，那配置就會非常簡單了，也能夠大量減輕應用服務器的壓力！
感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
原文鏈接：http://blog.csdn.net/afandaafandaafanda/article/details/45936475
