共計 6940 個字符，預計需要花費 18 分鐘才能閱讀完成。
行業資訊
數據庫
實現 SQL Server 原生數據從 XML 生成 JSON 數據的實例代碼怎么編寫
實現 SQL Server 原生數據從 XML 生成 JSON 數據的實例代碼怎么編寫，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
實現 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 /station3. 如何生成 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 ENDCREATE 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+ end3）查詢 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，那配置就會非常簡單了，也能夠大量減輕應用服務器的壓力！
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注丸趣 TV 行業資訊頻道，感謝您對丸趣 TV 的支持。
正文完
