实现SQL Server 原生数据从XML生成JSON数据的实例代码
发布时间 - 2026-01-11 00:22:01 点击率：次实现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，那配置就会非常简单了，也能够大量减轻应用服务器的压力！
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！
# SQL
# Server
# XML生成JSON数据
# 详解SQL
# 原生数据从XML生成JSON数据
# SQL SERVER 将XML变量转为JSON文本
# SQL Server中将数据导出为XML和Json方法分享
# 在SQL Server中将数据导出为XML和Json的方法
# php 备份数据库代码(生成word
# excel
# json
# xml
# sql)
# SqlServer将查询结果转换为XML和JSON
# 查询结果
# 就会
# 是在
# 放在
# 也能
# 希望能
# 写了
# 谢谢大家
# 数据库中
# 够大
# 通常都是
# 测试数据
# 数据格式
# John
# SURNAME
# Operators
# Paul
# Smith
# Fox
# Brown
相关栏目：
【
网站优化151355 】
【
网络推广146373 】
【
网络技术251813 】
【
AI营销90571 】
相关推荐：
详解Nginx + Tomcat 反向代理 负载均衡 集群 部署指南
Laravel如何使用Scope本地作用域_Laravel模型常用查询逻辑封装技巧【手册】
如何用狗爹虚拟主机快速搭建网站？
Laravel Session怎么存储_Laravel Session驱动配置详解
网站建设要注意的标准 促进网站用户好感度！
CSS3怎么给轮播图加过渡动画_transition加transform实现【技巧】
C#如何调用原生C++ COM对象详解
Laravel如何生成API文档？（Swagger/OpenAPI教程）
邀请函制作网站有哪些,有没有做年会邀请函的网站啊？在线制作，模板很多的那种？
韩国网站服务器搭建指南：VPS选购、域名解析与DNS配置推荐
什么是JavaScript解构赋值_解构赋值有哪些实用技巧
如何自定义建站之星网站的导航菜单样式？
nodejs redis 发布订阅机制封装实现方法及实例代码
php打包exe后无法访问网络共享_共享权限设置方法【教程】
Laravel如何实现用户注册和登录？（Auth脚手架指南）
如何确认建站备案号应放置的具体位置？
如何确保FTP站点访问权限与数据传输安全？
安克发布新款氮化镓充电宝：体积缩小 30%，支持 200W 输出
Laravel Seeder怎么填充数据_Laravel数据库填充器的使用方法与技巧
Laravel中间件如何使用_Laravel自定义中间件实现权限控制
JavaScript如何实现路由_前端路由原理是什么
JS弹性运动实现方法分析
Laravel软删除怎么实现_Laravel Eloquent SoftDeletes功能使用教程
*服务器网站为何频现安全漏洞？
Linux系统命令中screen命令详解
如何用搬瓦工VPS快速搭建个人网站？
如何在VPS电脑上快速搭建网站？
黑客如何利用漏洞与弱口令入侵网站服务器？
免费网站制作appp,免费制作app哪个平台好？
Laravel如何使用Spatie Media Library_Laravel图片上传管理与缩略图生成【步骤】
Laravel事件和监听器如何实现_Laravel Events & Listeners解耦应用的实战教程
Firefox Developer Edition开发者版本入口
Win11怎么设置默认图片查看器_Windows11照片应用关联设置
大学网站设计制作软件有哪些,如何将网站制作成自己app？
如何在局域网内绑定自建网站域名？
如何用AI帮你把自己的生活经历写成一个有趣的故事？
惠州网站建设制作推广,惠州市华视达文化传媒有限公司怎么样？
Laravel怎么设置路由分组Prefix_Laravel多级路由嵌套与命名空间隔离【步骤】
如何快速上传自定义模板至建站之星？
Android中Textview和图片同行显示(文字超出用省略号，图片自动靠右边)
ChatGPT怎么生成Excel公式_ChatGPT公式生成方法【指南】
Laravel怎么做缓存_Laravel Cache系统提升应用速度的策略与技巧
lovemo网页版地址 lovemo官网手机登录
Laravel全局作用域是什么_Laravel Eloquent Global Scopes应用指南
中国移动官方网站首页入口 中国移动官网网页登录
想要更高端的建设网站，这些原则一定要坚持！
美食网站链接制作教程视频,哪个教做美食的网站比较专业点？
如何快速配置高效服务器建站软件？
悟空浏览器如何设置小说背景色_悟空浏览器背景色设置【方法】
laravel怎么使用数据库工厂（Factory）生成带有关联模型的数据_laravel Factory生成关联数据方法