sqlserver如何生成xml文件(实现SQL Server 原生数据从XML生成JSON数据的实例代码)
类别:数据库 浏览量:358
时间:2022-01-23 02:38:11 sqlserver如何生成xml文件
实现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,那配置就会非常简单了,也能够大量减轻应用服务器的压力!
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
您可能感兴趣
- SqlServer 按时间段查询问题(SqlServer 按时间段查询问题)
- sqlserver查看某个库所有表(SQL Server怎么找出一个表包含的页信息Page)
- docker启动sqlserver(用docker运行postgreSQL的方法步骤)
- python2.7连接sqlserver数据库(Python使用sqlalchemy模块连接数据库操作示例)
- sqlserver 使用SSMS运行sql脚本的六种方法(sqlserver 使用SSMS运行sql脚本的六种方法)
- sqlserver索引实例(SQL Server索引的原理深入解析)
- sqlserver配置管理器不见了(SQL Server配置管理器无法连接到WMI提供程序)
- sqlserver数据库文件包括(详解SQL Server数据库状态和文件状态)
- sqlserver安装日志文件夹(SQL SERVER日志进行收缩的图文教程)
- 2021-10-24 10:26:05
- sqlserver 怎么排序(SQLserver排序规则基本概念探索)
- sqlserver新建连接服务器卡住(SQL Server成功与服务器建立连接但是在登录过程中发生错误的快速解决方案)
- sqlserver最大数据导入量(SQL Server批量插入数据案例详解)
- sqlserver非唯一索引汇总性能(详解SQL Server的聚焦过滤索引)
- sqlserver常用数据结构图(Sql Server数据库常用Transact-SQL脚本推荐)
- sqlserver备份还原数据(通过Windows批处理命令执行SQL Server数据库备份)
- 沪上这16所高校 萌新 礼包开箱 哪一款让你心动(沪上这16所高校萌新)
- 她救了被绑架的他,而这一切竟是一场阴谋...(她救了被绑架的他)
- 冬季养殖这6种阴生植物,方便又好养,你家有么(冬季养殖这6种阴生植物)
- 阴生植物为什么不怕照不到阳光(阴生植物为什么不怕照不到阳光)
- 阴生环境 耐阴地被植物,你知道哪些(阴生环境耐阴地被植物)
- 常见的喜阴植物有哪些 养室内盆栽就在这里选(常见的喜阴植物有哪些)
热门推荐
- python mysql配置(详解python校验SQL脚本命名规则)
- python列表切片和分片(Python列表切片操作实例总结)
- 安装SQL Server 2016出错提示:需要安装oracle JRE7 更新 51(64位)或更高版本问题的解(安装SQL Server 2016出错提示:需要安装oracle JRE7 更新 5164位或更高版本问题的解)
- python如何将xls转成xlsx(Python这样操作能存储100多万行的xlsx文件)
- python中的isi(深入浅析Python 中 is 语法带来的误解)
- pythonexcel生成报表(python生成每日报表数据Excel并邮件发送的实例)
- 如何设置bootstrap轮播图的速度(bootstrap实现轮播图效果)
- css3中基本选择符(CSS3 clip-path 用法介绍详解)
- 将docker中的镜像打包教程(本地使用docker打包部署镜像的方法)
- mysql 使用小结(Mysql Online DDL的使用详解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9