完结小说,完美世界辰东,我欲封天

主頁(yè) > 知識(shí)庫(kù) > 實(shí)現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實(shí)例代碼

實(shí)現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實(shí)例代碼

SQL Server 是關(guān)系數(shù)據(jù)庫(kù)，查詢結(jié)果通常都是數(shù)據(jù)集，但是在一些特殊需求下，我們需要XML數(shù)據(jù)，最近這些年，JSON作為WebAPI常用的交換數(shù)據(jù)格式，那么數(shù)據(jù)庫(kù)如何生成JSON數(shù)據(jù)呢？今天就寫了一個(gè)DEMO.

1.創(chuàng)建表及測(cè)試數(shù)據(jù)

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.查詢結(jié)果集

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

結(jié)果：

2.查詢xml數(shù)據(jù)

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

結(jié)果：

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數(shù)據(jù)

1）創(chuàng)建輔助函數(shù)

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 
  ) 
)

結(jié)果：

[ {"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"}]}]}]

總結(jié)：

JSON作為靈活的Web通信交換架構(gòu)，如果把配置數(shù)據(jù)存放在數(shù)據(jù)庫(kù)中，直接獲取JSON，那配置就會(huì)非常簡(jiǎn)單了，也能夠大量減輕應(yīng)用服務(wù)器的壓力！

感謝閱讀，希望能幫助到大家，謝謝大家對(duì)本站的支持！

您可能感興趣的文章:

SQL SERVER 將XML變量轉(zhuǎn)為JSON文本
SQL Server中將數(shù)據(jù)導(dǎo)出為XML和Json方法分享
在SQL Server中將數(shù)據(jù)導(dǎo)出為XML和Json的方法
php 備份數(shù)據(jù)庫(kù)代碼(生成word,excel,json,xml,sql)
SqlServer將查詢結(jié)果轉(zhuǎn)換為XML和JSON

標(biāo)簽：宿州防疫戰(zhàn)設(shè) 天水益陽(yáng) 威海七臺(tái)河來(lái)賓銅仁

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《實(shí)現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實(shí)例代碼》，本文關(guān)鍵詞實(shí)現(xiàn),SQL,Server,原生,數(shù),據(jù)從,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。