实现SQL Server 原生数据从XML生成JSON数据的实例代码
SQL Server 是关系数据库，查询结果通常都是数据集，但是在一些特殊需求下，我们需要XML数据，最近这些年，JSON作为WebAPI常用的交换数据格式，那么数据库如何生成JSON数据呢？今天就写了一个DEMO.
1.创建表及测试数据
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.查询结果集
结果：
2.查询xml数据
结果：
13
Phoenix
AZ
3.3000000e+001
1.1200000e+002
13
1
5.7400002e+001
3.1000000e-001
50
John "The Fox"
Brown
13
7
9.1699997e+001
5.1500001e+000
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
51
Paul
Smith
52
Michael
Williams
50
John "The Fox"
Brown
66
Caribou
ME
4.7000000e+001
6.8000000e+001
66
1
6.6999998e+000
2.0999999e+000
51
Paul
Smith
66
7
6.5800003e+001
4.5200000e+000
51
Paul
Smith
3.如何生成JSON数据
1）创建辅助函数
<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
3）查询sql
结果：
总结：
JSON作为灵活的Web通信交换架构，如果把配置数据存放在数据库中，直接获取JSON，那配置就会非常简单了，也能够大量减轻应用服务器的压力！
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！ （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
