SQL Server 原生数据从XML生成JSON数据

最新推荐文章于 2021-11-11 09:13:51 发布

feng1456

最新推荐文章于 2021-11-11 09:13:51 发布

阅读量1.6k

点赞数

分类专栏： SqlServer数据库文章标签： sql server JSON

本文链接：https://blog.csdn.net/afandaafandaafanda/article/details/45936475

版权

SqlServer数据库专栏收录该内容

38 篇文章 0 订阅

订阅专栏

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