这里有新鲜出炉的SQL Server教程,程序狗速度看过来!
SQL是英文Structured Query Language的缩写,意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系,进行沟通。按照ANSI(美国国家标准协会)的规定,SQL被作为关系型数据库管理系统的标准语言。
SQL Server 即 Microsoft SQL Server 。
这篇文章主要介绍了实现SQL Server 原生数据从XML生成JSON数据的实例代码的相关资料,需要的朋友可以参考下
实现SQL Server 原生数据从XML生成JSON数据的实例代码
SQL Server 是关系数据库,查询结果通常都是数据集,但是在一些特殊需求下,我们需要XML数据,最近这些年,JSON作为WebAPI常用的交换数据格式,那么数据库如何生成JSON数据呢?今天就写了一个DEMO.
1.创建表及测试数据
- 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.查询结果集
- 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数据
- 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
结果:
- <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)创建辅助函数
- 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
3)查询sql
- 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
- )
- )
结果:
- [ {"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://www.phperz.com/article/17/0903/339118.html