實現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實例代碼
更新時間：2017年03月25日 14:48:39 作者：feng1456
這篇文章主要介紹了實現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實例代碼的相關(guān)資料,需要的朋友可以參考下
實現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實例代碼
SQL Server 是關(guān)系數(shù)據(jù)庫，查詢結(jié)果通常都是數(shù)據(jù)集，但是在一些特殊需求下，我們需要XML數(shù)據(jù)，最近這些年，JSON作為WebAPI常用的交換數(shù)據(jù)格式，那么數(shù)據(jù)庫如何生成JSON數(shù)據(jù)呢？今天就寫了一個DEMO.
1.創(chuàng)建表及測試數(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ù)庫中，直接獲取JSON，那配置就會非常簡單了，也能夠大量減輕應(yīng)用服務(wù)器的壓力！
感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
相關(guān)文章
在?SQL?Server?中,備份通常存儲在擴(kuò)展名為?.BAK?的文件中,當(dāng)服務(wù)器或數(shù)據(jù)庫文件發(fā)生事故或問題需要恢復(fù)數(shù)據(jù)時,備份非常有用,但是,SQL?數(shù)據(jù)庫備份?(.BAK)?文件也可能由于各種因素而損壞,本文介紹了SQL?Server?.BAK?文件損壞的解決方法,需要的朋友可以參考下2024-08-08
SqlServer批量備份多個數(shù)據(jù)庫且刪除3天前的備份
這篇文章主要介紹了SqlServer批量備份多個數(shù)據(jù)庫且刪除3天前的備份,需要的朋友可以參考下2017-09-09
SQLServer查詢歷史執(zhí)行記錄的方法實現(xiàn)
有的時候,需要知道近段時間SQLSERVER執(zhí)行了什么語句,本文主要介紹了SQLServer查詢歷史執(zhí)行記錄的方法實現(xiàn),具有一定的參考價值,感興趣的可以了解一下2023-09-09
使用BULK INSERT大批量導(dǎo)入數(shù)據(jù) SQLSERVER
使用BULK INSERT大批量導(dǎo)入數(shù)據(jù) SQLSERVER,需要的朋友可以參考下。2011-12-12
SQL Server中將數(shù)據(jù)導(dǎo)出為XML和Json方法分享
這篇文章主要介紹了SQL Server中將數(shù)據(jù)導(dǎo)出為XML和Json方法分享,本文使用PowerShell中的BCP命令實現(xiàn)導(dǎo)出為文件,需要的朋友可以參考下2015-02-02
SQL Server免費版的安裝以及使用SQL Server Management Studio(SSMS)連接數(shù)據(jù)庫的
這篇文章主要介紹了SQL Server免費版的安裝以及使用SQL Server Management Studio(SSMS)連接數(shù)據(jù)庫的圖文方法,需要的朋友可以參考下2020-02-02
這篇文章主要給大家介紹了關(guān)于sql?server中觸發(fā)器用法的相關(guān)資料,SQL Server觸發(fā)器是一種特殊類型的存儲過程,它們在數(shù)據(jù)庫中的表上自動執(zhí)行,需要的朋友可以參考下2024-03-03
這篇文章主要介紹了SQL報錯盲注詳解,包括SQL報錯函數(shù)，SQL報錯盲注payload構(gòu)造，本文通過實例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-07-07
SQL SERVER中SELECT和SET賦值相同點與不同點(推薦)
SELECT和SET在SQL SERVER中都可以用來對變量進(jìn)行賦值，但其用法和效果在一些細(xì)節(jié)上有些不同。今天小編給大家分享SQL SERVER中SELECT和SET賦值相同點與不同點，感興趣的朋友一起看看吧2019-12-12
Sql Server 壓縮數(shù)據(jù)庫日志文件的方法
Sql Server 日志 _log.ldf文件太大，數(shù)據(jù)庫文件有500g,日志文件也達(dá)到了500g,占用磁盤空間過大，且可能影響程序性能，需要壓縮日志文件，下面小編給大家講解下Sql Server 壓縮數(shù)據(jù)庫日志文件的方法，感興趣的朋友一起看看吧2022-11-11