實(shí)現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實(shí)例代碼
更新時(shí)間：2017年03月25日 14:48:39 作者：feng1456
這篇文章主要介紹了實(shí)現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實(shí)例代碼的相關(guān)資料,需要的朋友可以參考下
實(shí)現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實(shí)例代碼
SQL Server 是關(guān)系數(shù)據(jù)庫，查詢結(jié)果通常都是數(shù)據(jù)集，但是在一些特殊需求下，我們需要XML數(shù)據(jù)，最近這些年，JSON作為WebAPI常用的交換數(shù)據(jù)格式，那么數(shù)據(jù)庫如何生成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ù)庫中，直接獲取JSON，那配置就會(huì)非常簡單了，也能夠大量減輕應(yīng)用服務(wù)器的壓力！
感謝閱讀，希望能幫助到大家，謝謝大家對(duì)本站的支持！
相關(guān)文章
詳解SQL Server 中 JSON_MODIFY 的使用
SQL Server 從 2016 開始支持了一些 JSON操作，最近的項(xiàng)目里也是好多地方字段直接存成了 JSON，需要了解一下怎么在SQL Server 中操作 JSON.這篇文章主要介紹了SQL Server 中 JSON_MODIFY 的使用,需要的朋友可以參考下2019-11-11
本文給大家收集整理了些關(guān)于sql數(shù)據(jù)操作語句的相關(guān)資料，介紹的非常詳細(xì)，具有參考借鑒價(jià)值，特此分享在腳本之家網(wǎng)站供大家參考2015-11-11
SQL學(xué)習(xí)筆記七函數(shù) 數(shù)字，日期，類型轉(zhuǎn)換，空值處理，case
SQL學(xué)習(xí)筆記七函數(shù) 數(shù)字，日期，類型轉(zhuǎn)換，空值處理，case 使用說明2011-08-08
談?wù)凾empdb對(duì)SQL Server性能優(yōu)化有何影響
由于tempdb是SQLServer的系統(tǒng)數(shù)據(jù)庫一直都是SQLServer的重要組成部分，用來存儲(chǔ)臨時(shí)對(duì)象，在數(shù)據(jù)庫中起到舉足輕重的作用，此篇文章給大家?guī)韙empdb對(duì)sql server性能優(yōu)化的影響，感興趣的朋友參考下2015-11-11
用戶"sa"登陸失敗 SQLServer 錯(cuò)誤18456的解決方法
sqlserver下用sa登錄提示18456錯(cuò)誤的解決方法。2009-11-11
我現(xiàn)在想把S_USER表中的ACCOUNT批量修改成S_PERSON的ACCOUNT，那么就可以參考下面的語句，要找到對(duì)應(yīng)的字段2019-11-11
SQL學(xué)習(xí)筆記八 索引，表連接，子查詢，ROW_NUMBER
SQL學(xué)習(xí)筆記八 索引，表連接，子查詢，ROW_NUMBER，學(xué)習(xí)sqlserver的朋友可以看下。2011-08-08
SQLServer 數(shù)據(jù)庫中如何保持?jǐn)?shù)據(jù)一致性
復(fù)制是SQLServer數(shù)據(jù)庫中保持?jǐn)?shù)據(jù)一致性的一種手段。2009-06-06
SQLserver查詢數(shù)據(jù)類型為ntext是空或NULL值的方法
SQLserver查詢數(shù)據(jù)類型為ntext是空或NULL值的方法2010-07-07
SQLServer 游標(biāo)的創(chuàng)建和使用基本步驟
游標(biāo)主要用于存儲(chǔ)過程、觸發(fā)器或T-SQL腳本中,當(dāng)需要遍歷查詢結(jié)果集中的每一行數(shù)據(jù)并進(jìn)行操作時(shí),游標(biāo)就顯得非常有用,本文給大家介紹SQLServer 游標(biāo)的創(chuàng)建和使用基本步驟,感興趣的朋友一起看看吧2024-08-08