您好,登錄后才能下訂單哦!
實現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實例代碼怎么編寫,很多新手對此不是很清楚,為了幫助大家解決這個難題,下面小編將為大家詳細講解,有這方面需求的人可以來學習下,希望你能有所收獲。
實現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實例代碼
SQL Server 是關系數(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"}]}]}]
JSON作為靈活的Web通信交換架構,如果把配置數(shù)據(jù)存放在數(shù)據(jù)庫中,直接獲取JSON,那配置就會非常簡單了,也能夠大量減輕應用服務器的壓力!
看完上述內(nèi)容是否對您有幫助呢?如果還想對相關知識有進一步的了解或閱讀更多相關文章,請關注億速云行業(yè)資訊頻道,感謝您對億速云的支持。
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內(nèi)容。