安科网

实现SQL Server 原生数据从XML生成JSON数据的实例代码

xyzxiafancai

xyzxiafancai

2019-04-09

关注 关注

实现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

结果：

实现SQL Server 原生数据从XML生成JSON数据的实例代码

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，那配置就会非常简单了，也能够大量减轻应用服务器的压力！

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

大数据 table

xyzxiafancai

xyzxiafancai

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

docker容器与宿主机的数据交互方式总结

在生产环境中使用 Docker ，往往需要对数据进行持久化，或者需要在多个容器之间进行数据共享，这必然涉及容器的数据管理操作。docker cp :用于容器与主机之间的数据拷贝。当删除Docker容器，并通过该镜像重新启动时，之前的更改将会丢失。在Dock

yangkang 2020-11-09

分布式文档存储数据库之MongoDB备份与恢复的实践详解

　　为什么要备份？　　备份的目的是对数据做冗余的一种方式，它能够让我们在某种情况下保证最少数据的丢失；之前我们对mongodb做副本集也是对数据做冗余，但是这种在副本集上做数据冗余仅仅是针对系统故障或服务异常等一些非人为的故障发生时，保证数据服务的可用性；

lbyd0 2020-11-17

分布式文档存储数据库之MongoDB分片集群的问题

　　1、什么是分片？比如用户要查询年龄大于30的用户，该怎么查询呢？而年龄大于30的用户的数据，可能server1上有一部分数据，server2上有部分数据，我们怎么才能够把所有满足条件的数据全部查询到呢？

sushuanglei 2020-11-12

详解Vue数据驱动原理

Vue区别于传统的JS库，例如JQuery，其中一个最大的特点就是不用手动去操作DOM，只需要对数据进行变更之后，视图也会随之更新。比如你想修改div#app里的内容:. 在代码层面上的最大区别就是，JQuery直接对DOM进行了操作，而Vue则对数据进行

85477104 2020-11-17

vue+echarts+datav大屏数据展示及实现中国地图省市县下钻功能

随着前端技术的飞速发展，大数据时代的来临，我们在开发项目时越来越多的客户会要求我们做一个数据展示的大屏，可以直观的展示用户想要的数据，同时炫酷的界面也会深受客户的喜欢。大屏展示其实就是一堆的图表能够让人一目了然地看到该系统下的一些基本数据信息的汇总，也会有

KANSYOUKYOU 2020-11-16

THINKPHP5分页数据对象处理过程解析

在用到THINKPHP5的分页的时候，我们可以发现获取的数据是对象，如果我们要对数据进行循环增加数据就实现不了。V5.0.9版本开始 支持分页类后数据直接each遍历处理，方便修改分页后的数据，而不是只能通过模型的获取器来补充字段。

wushengyong 2020-10-28

需要知识的后深度学习时代，如何高效自动构建知识图谱

前沿的知识图谱自动构建技术有哪些？这篇文章将逐一解答这些问题。二者展示的信息量是差不多的，但右边这种看起来更加直观。而且，随着文本篇幅的增长，这种优势会体现得更加明显。其中，图的节点代表现实世界中存在的“实体”，图的边则代表实体之间的“关系”。基于知识图谱

lizhengjava 2020-11-13

人工智能技术如何落地交通出行？

在城市交通领域，有AI红绿灯控制，街道交通智能监测、智能公交车站，以及智能高速，这些领域都已经渗透了人工智能。围绕自动驾驶和车路协同也已经在全国多个地区进入商用测试阶段。那么日常还有哪些出行场景是应用到了AI技术的呢?大大降低了人工运营维护成本，人工审核降

星月情缘 2020-11-13

将云技术带入数据中心-走向数据驱动型业务的旅程

COVID-19产生的大量数据正在为企业创造新的增长机会，但拥有合适的基础设施对于有效应对这场数据风暴至关重要。Gartner最近警告说：“数据和分析领导者必须为多云和跨云部署的复杂性做好准备，以避免潜在的性能问题、计划外的成本超支和集成工作中的困难”。负

huangxiaoyun00 2020-11-13

联想持续发力智能物联，构建新基建时代下的行业新引擎

联想Tech World 2020创新科技大会于今日开幕。

zhoushuntian 2020-11-09

数据科学面试中应了解的十种机器学习概念

如您本文转载自公众号“读芯术”。如您所知，数据科学和机器学习必须提供无穷无尽的信息和知识。话虽如此，大多数公司都只测试少数核心思想。这是因为这十个概念是更复杂的思想和概念的基础。您可能想知道为什么我什至不愿意将其放入，因为它是如此的基础。换句话说，更加重视

luyong0 2020-11-08

Forrester发布全栈公有云开发平台报告，腾讯云再次入选领导者象限

今日获悉，国际领先行业咨询机构Forrester发布《2020年Q4中国全栈公有云开发平台Wave报告》显示，腾讯云再次入选公有云开发平台领导者象限。在2018年同主题报告中，腾讯云也曾入选。本次报告通过33项评估标准，针对中国市场最具代表性的12个厂商进

腾讯soso团队 2020-11-06

雾计算在物联网中的应用

雾计算是指一种分散的计算结构。资源被放置在数据源和云之间的逻辑位置。雾计算的优点之一是可以在同一时间上维持多用户连接的状态。本质上，它提供了与基于云的解决方案相同的网络和服务，但是它增加了分散网络的安全性。随着物联网的发展，越来越多的设备被添加到网络中。据

Apsaravod 2020-11-05

AI格局正在从“数据”转变为“知识”

半个多世纪以前就引发了人工智能革命。我们看到的最常见的AI业务策略是围绕数据构建的。我们认为专有数据是AI公司目前很具战略意义的护城河，但在未来几年中，专有数据将不再是一种独特的资产，从而使专有数据差异化的可持续性降低。因此，我们希望重点从基于数据的AI策

PeterChangyb 2020-11-05

SAP AMDP介绍 - ABAP托管的HANA数据库过程

最近Jerry的处境可以用本世纪初，八零后刚上大学时校园内风靡的一款FPS游戏名称来形容: 《半条命》. 为了避免让汪子熙这个公众号成为神经外科前中颅底亚专业医学知识的普及号，咱们还是继续聊SAP技术吧。随着ABAP 7.40 SP05的发布，SAP AB

gaobudong 2020-11-04

数据骗子无处不在，教你拆穿所谓“万金油”

数据分析师、机器学习/人工智能工程师、统计学家，这样的头衔是不是听起来很高大上?但小心别被骗了!高薪诱惑之下，不少数据骗子也隐藏在其中，这些骗子毁了遵纪守法的数据专业人士的好名声。第一点线索就是，他们无法理解分析学和统计学是两个截然不同的学科。这样的p值没

wwwjun 2020-11-02

模型数据出现偏差怎么办？一文了解机器学习中的7种数据偏差类型

机器学习中的数据偏差是一种错误，其中数据集的某些元素的权重和/或表示程度高于其他元素。偏置数据集不能准确表示模型的用例，从而导致结果偏斜、精度低和分析错误。通常，机器学习项目的培训数据必须代表现实世界。数据偏差可能发生在一系列领域，从人类报告和选择偏差到算

gyunwh 2020-11-02

机器学习中处理缺失值的9种方法

它是任何数据科学或机器学习项目的关键。在大多数情况下，当我们从不同的资源收集数据或从某处下载数据时，几乎有95%的可能性我们的数据中包含缺失的值。我们不能对包含缺失值的数据进行分析或训练机器学习模型。这就是为什么我们90%的时间都花在数据预处理上的主要原因

EchoYY 2020-10-31

人工智能和机器学习如何从物联网数据中提取关键见解

过去几年，围绕物联网的大部分讨论都集中在连网设备本身——它们是什么、有多少以及如何保护它们。虽然所有这些小端点都很重要，但在物联网中更重要的是这些设备所生成的大量数据，以及通过分析可以从中获得的业务见解。这些缺点在物联网环境中更加严重，在物联网环境中，大量

dingyahui 2020-10-30

IT打工人，AI来“抢”你饭碗了！这次从数据中心下手

ningwentao 2020-10-30
xyzxiafancai

xyzxiafancai

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号