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语句创建窗口布局(SQL Server 2012 开窗函数)
- sqlserver查看数据表更新时间(SQL Server实现显示每个类别最新更新数据的方法)
- sql启动错误代码1814(SQLMSSQLSERVER服务启动错误代码3414的解决方法)
- sqlserver连接字符串函数(SQL SERVER 2012新增函数之字符串函数FORMAT详解)
- sqlserver数据库基本操作(SQL Server四个系统表的知识讲解)
- sqlserver数据库技术及应用教程(SQLServer2019 数据库的基本使用之图形化界面操作的实现)
- sqlserver查看表和存储过程的架构(SQL Server 远程更新目标表数据的存储过程)
- sqlserver备份整表数据的语句(SqlServer批量备份多个数据库且删除3天前的备份)
- sqlserver日志目录是否过大(SQL Server利用sp_spaceused如何查看表记录存在不准确的情况)
- sqlserver安装使用教程(SQL Server 2019下载与安装教程自定义安装)
- sqlserver索引实例(SQL Server索引的原理深入解析)
- SqlServer 复制中将大事务分成小事务分发的方法(SqlServer 复制中将大事务分成小事务分发的方法)
- 修改sqlserver数据库所有者
- sqlserver数据库实时同步(SQL Server实时同步更新远程数据库遇到的问题小结)
- 2021-10-24 10:26:05
- sqlserver 怎么排序(SQLserver排序规则基本概念探索)
- 终于来了,淘宝更改账户名测试中,快去看看你能不能修改(淘宝更改账户名测试中)
- 淘宝支持账号名修改,网友 终于可以 重新做人 了(淘宝支持账号名修改)
- 盘点那些年让人称奇的年终奖 最后一个赢辣条毫无悬念(盘点那些年让人称奇的年终奖)
- 你还没有升职吗 他竟因为几套激励理论,升职了(你还没有升职吗)
- 某知名企业绩效管理体系及薪酬分配体系操作手册(某知名企业绩效管理体系及薪酬分配体系操作手册)
- 职场人改不掉这4个习惯,只会越混越穷,一辈子也翻不了身(职场人改不掉这4个习惯)
热门推荐
- 织梦模板被挂了怎么办(详解简单修改DEDECMS织梦模板防止网站挂马的教程)
- linux内核从原理到代码详解(探究一个LED如何入门Linux内核)
- python什么是深拷贝什么是浅拷贝(Python深拷贝与浅拷贝用法实例分析)
- mysqlexplain的用法(MySQL SHOW STATUS语句的使用)
- html注册表单验证代码(JavaScript+html实现前端页面随机二维码验证)
- zabbix支持oracleodbc功能吗(使用zabbix监控oracle数据库的方法详解)
- laravel队列过期时间(laravel 修改记住我功能的cookie保存时间的方法)
- 云服务器cvm什么意思(什么是云服务器cvm?怎么与普通物理服务器进行)
- 做网站是使用nginx还是apache(web服务器软件Apache与Nginx的对比分析)
- tensorflow自定义初始化(Tensorflow分类器项目自定义数据读入的实现)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9