服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - 实现SQL Server 原生数据从XML生成JSON数据的实例代码

实现SQL Server 原生数据从XML生成JSON数据的实例代码

2020-05-19 13:47feng1456 Sql Server

这篇文章主要介绍了实现SQL Server 原生数据从XML生成JSON数据的实例代码的相关资料,需要的朋友可以参考下

实现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

结果：

实现SQL Server 原生数据从XML生成JSON数据的实例代码

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)
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

 

?
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

  • SQLSERVER
  • JSON
  • XML
  • 原生数据

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    2005 　 18052 　 1355 　 3456 　 29506 　 9003 　 5120 　 21002 　 5173 　 15138 　 15404 　 错误5123 　 SQL导入 　 sql导出 　 新增 　 sp_executesql 　 xp_cmdshell 　 Transact_SQL 　 手册 　 SQL Server2005 　 网络备份 　 日期查询 　 数据库开发 　 SQL Server 2000 　 经典语句 　 删除重复记录 　 收缩日志 　 排序规则 　 基本函数 　 加密函数 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图