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

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

时间: 2017-03-23

php 备份数据库代码(生成word,excel,json,xml,sql)

单表备份代码: 复制代码 代码如下: <?php    class Db    {        var $conn; function Db($host="localhost",$user="root",$pass="root",$db="test")        {          if(!$this->conn=mysql_connect($host,$user,$pass))          die(

SQL SERVER 将XML变量转为JSON文本

废话不多说了,直接给大家贴代码了. -- create function create function [dbo].[fnXmlToJson] (@XmlData xml) returns nvarchar(max) as begin return (select stuff( (select * from (select ',{'+ stuff( (select ',"'+ coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'&

SQL Server中将数据导出为XML和Json方法分享

有时候需要一次性将SQL Server中的数据导出给其他部门的也许进行关联或分析,这种需求对于SSIS来说当然是非常简单,但很多时候仅仅需要一次性导出这些数据而建立一个SSIS包就显得小题大做,而SQL Server的导入导出工具其中BUG还是蛮多的,最简单的办法是BCP.  数据导出为XML 在SQL Server 2005之后提供了一个for xml子句在关系数据库中原生支持XML.通过该命令可以将二维关系结果集转换为XML,通过BCP就可以将数据存为XML了. 例如下面的数据: 我们可以通

在SQL Server中将数据导出为XML和Json的方法

有时候需要一次性将SQL Server中的数据导出给其他部门的也许进行关联或分析,这种需求对于SSIS来说当然是非常简单,但很多时候仅仅需要一次性导出这些数据而建立一个SSIS包就显得小题大做,而SQL Server的导入导出工具其中BUG还是蛮多的,最简单的办法是BCP. 数据导出为XML 在SQL Server 2005之后提供了一个for xml子句在关系数据库中原生支持XML.通过该命令可以将二维关系结果集转换为XML,通过BCP就可以将数据存为XML了. 例如下面的数据: 我们可以通过

SqlServer将查询结果转换为XML和JSON

很久之前用到的,现在整理在这,里面一些代码来源于网上,不过有些bug已被我修改了. 1.查询结果转XML DECLARE @ParameterSQL NVARCHAR(MAX)='SELECT * FROM table'; DECLARE @SQL NVARCHAR(MAX) DECLARE @XMLString VARCHAR(MAX) DECLARE @XML XML DECLARE @Paramlist NVARCHAR(1000) SET @Paramlist = N'@XML XML

C# XML与Json之间相互转换实例详解

对于这转换其实很简单,其中最重要的就是先要引用类库.可以到官网进行下载引用http://json.codeplex.com. XML转换为Json字符串 复制代码 代码如下: string xml = @"<?xml version=""1.0"" standalone=""no""?>                             <root>                   

如何在JS中实现相互转换XML和JSON

开发中有时候会遇到XML和JSON相互转换,要求在JS中使用,网上找了好多,竟然每一个好用的,要么缺胳膊少腿,要么词不达意,太没天理了,果断自己实现一个. JSON与XML的区别比较 1.定义介绍 (1).XML定义 扩展标记语言 (Extensible Markup Language, XML) ,用于标记电子文件使其具有结构性的标记语言,可以用来标记数据.定义数据类型,是一种允许用户对自己的标记语言进行定义的源语言. XML使用DTD(document type definition)文档类

php实现将数组转换为XML的方法

本文实例讲述了php实现将数组转换为XML的方法.分享给大家供大家参考.具体如下: 1. php代码如下: <?php class A2Xml { private $version = '1.0'; private $encoding = 'UTF-8'; private $root = 'root'; private $xml = null; function __construct() { $this->xml = new XmlWriter(); } function toXml($da

Python实现对象转换为xml的方法示例

本文实例讲述了Python实现对象转换为xml的方法.分享给大家供大家参考,具体如下: # -*- coding:UTF-8 -*- ''''' Created on 2010-4-20 @author: 忧里修斯 ''' import xml.etree.ElementTree as ET import xml.dom.minidom as minidom from addrbook.domain import Person class Converter(object): ''''' 实现P

ASP.NET中XML转JSON的方法实例

本文实例讲述了ASP.NET中XML转JSON的方法,分享给大家供大家参考.具体如下: 一般在许多应用程序中都将数据存储为XML的格式,而且会将数据以JSON的格式发送到客户端以做进一步处理.要实现这一点,它们必须将XML格式转换为JSON格式. XML转JSON代码如下: 复制代码 代码如下: private static string XmlToJSON(XmlDocument xmlDoc)  {      StringBuilder sbJSON = new StringBuilder(

解决使用json-lib包实现xml转json时空值被转为空中括号的问题

项目数据库中出现许多值为中括号[]的数据,测试报了bug,经过排查是因为使用了json-lib 的jar包导致.json-lib在将xml字符串转换为json格式的过程中,会将空值转化了[],现在就简单的总结一下,两种xml转json包的异同.可能是因为此类问题比较初级,所以网上虽然有人提出此类问题,但是没有发现回答.为了方便像我一样的初级程序员能尽快的发现和解决问题,写了这篇博文. 网上能查到的xml转json的jar包大部分是net.sf.json-lib,这个包在json解析方面应用较多,