实现SQL Server 原生数据从XML生成JSON数据的实例代码
作者：feng1456 发布时间：2024-01-16 01:46:03
标签：SQLServer,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://blog.csdn.net/afandaafandaafanda/article/details/45936475
0
投稿猜你喜欢
- 本文实例讲述了js找出5个数中最大的一个数和倒数第二大的数实现方法。分享给大家供大家参考，具体如下：最大的一个数 ：方法1：<scri
- np.nonzero函数是numpy中用于得到数组array中非零元素的位置（数组索引）的函数。一般来说，通过help（np.nonzero
前言MySQL 服务器正确安装以后，可以通过命令行管理工具或者图形化的管理工具来操作 MySQL 数据库。MySQL 图形化管理工具极大地方- python 列表和链表的区别python 中的 list 并不是我们传统意义上的列表，传统列表——通常也叫作链表(linked list)
- 求一个算式a=1b=2c=3 print c*(a/b)运行结果总是0，反复检查拆开以后，发现在Python里，整数初整数，只能得
- 关于Python的文件遍历，大概有两种方法，一种是较为便利的os.walk()，还有一种是利用os.listdir()递归遍历。方法一：利用
- 本文实例讲述了PHP实现向关联数组指定的Key之前插入元素的方法。分享给大家供大家参考，具体如下：PHP 关联数组可以通过三种方式插入新元素
- 本文实例为大家分享了python脚本筛选出两个文件中重复的行数，供大家参考，具体内容如下'''查找A文件中，与B文件
- 一提到python，大家经常会提到爬虫，爬虫近来兴起的原因我觉得主要还是因为大数据的原因，大数据导致了我们的数据不在只存在于自己的服务器，而
- router 动态路由清除重置matcher可达到路由还原效果在用户退出时调用 resetRouter(router) 即可还原路由impo
- 字符x字符 x\\反斜线字符\0n带有八进制值 0 的字符 n (0 <=
- 1. ASCII 返回与指定的字符对应的十进制数; SQL> select ascii(A) A,ascii(a) a,as
- 前言正则表达式是一个特殊的字符序列，它能帮助你方便的检查一个字符串是否与某种模式匹配。Python 自1.5版本起增加了re 模块，它提供
- 本文实例讲述了Python基于动态规划算法解决01背包问题。分享给大家供大家参考，具体如下：在01背包问题中，在选择是否要把一个物品加到背包
- 前言本文主要给大家介绍了关于linux/mac安装mysql忘记密码的相关解决办法，分享出来供大家参考借鉴，下面话不多说了，来一起看看详细的
- Python列表和字典前面我们了解了 “大O表示法” 以及对不同的算法的评估，下面来讨论下 Python 两种内置数据类型有关的各种操作的大
- 本文实例分析了Python字符串格式化输出方法。分享给大家供大家参考，具体如下：我们格式化构建字符串可以有3种方法：1 元组占位符m = &
- 主要实现功能1、用户输入用户名，在用户名文件中查找对应的用户，若无对应用户名则打印输入错误2、用户名输入正确后，进行密码匹配。输入密码正确则
- 如果您想详细了解eval和JSON请参考以下链接：eval ：https://developer.mozilla.org/En/
- 在你的程序初始化时使用如下代码： <?php $Php2Html_FileUrl = $_SERVER["REQU
