主页 > 知识库 > 实现SQL Server 原生数据从XML生成JSON数据的实例代码

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

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

您可能感兴趣的文章:
  • SQL SERVER 将XML变量转为JSON文本
  • SQL Server中将数据导出为XML和Json方法分享
  • 在SQL Server中将数据导出为XML和Json的方法
  • php 备份数据库代码(生成word,excel,json,xml,sql)
  • SqlServer将查询结果转换为XML和JSON

标签:宿州 防疫战设 天水 益阳 威海 七台河 来宾 铜仁

巨人网络通讯声明:本文标题《实现SQL Server 原生数据从XML生成JSON数据的实例代码》,本文关键词  实现,SQL,Server,原生,数,据从,;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 下面列出与本文章《实现SQL Server 原生数据从XML生成JSON数据的实例代码》相关的同类信息!
  • 本页收集关于实现SQL Server 原生数据从XML生成JSON数据的实例代码的相关信息资讯供网民参考!
  • 推荐文章