程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> 實現SQL Server 原生數據從XML生成JSON數據的實例代碼

實現SQL Server 原生數據從XML生成JSON數據的實例代碼

編輯:MSSQL

實現SQL Server 原生數據從XML生成JSON數據的實例代碼。本站提示廣大學習愛好者:(實現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,那配置就會非常簡單了,也能夠大量減輕應用服務器的壓力!

感謝閱讀,希望能幫助到大家,謝謝大家對本站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved