实现结构化查询语言服务器原生数据从可扩展标记语言生成JSON数据的实例代码怎么编写,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
实现SQL Server 原生数据从XML生成JSON数据的实例代码
结构化查询语言服务器是关系数据库,查询结果通常都是数据集,但是在一些特殊需求下,我们需要可扩展标记语言数据,最近这些年,JSON作为WebAPI常用的交换数据格式,那么数据库如何生成JSON数据呢?今天就写了一个演示。
1.创建表及测试数据
SETNOCOUNTONIFOBJECT _ ID(' STATS ')是notnulldropdatestatsifobject _ ID(' STATS ')是notnulldropdatestassifoobject _ ID(' OPERATORS ')是notnulldropdateoperatorsifobject _ ID(' REVIEWS ')是notnulldropdatetablereviews-createnandpopulatetableartiagestasss(IDINTEGERPRIMARYKEY,CITYNVARCHAR(20),STATECHAR(2),LAT_NREAL,LONG _ WREAL);insertintostatusvalues(13 ')凤凰,' AZ ',33,112);insertintostatusvalues(44 ')丹佛,' CO ',40,105);insertintostatusvalues(66 ')驯鹿,' ME ',47,68);-createandpopulatetablewith运算符createtableoperators(IDINTEGERPRIMARYKEY,NAMENVARCHAR(20),SURNAMENVARCHAR(20));INSERTINTOOPERATORSVALUES(50,' John ' FORx ' ',' Brown ');INSERTINTOOPERATORSVALUES(51,' Paul ',' Smith ');INSERTINTOOPERATORSVALUES(52,' Michael ',' Williams ');-createandpopulatetablewithenormalized temperationdatacreatetablestats(stats _ idintegerferencessstats(ID),MONTHINTEGER
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,那配置就会非常简单了,也能够大量减轻应用服务器的压力!
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注行业资讯频道,感谢您对的支持。
内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/145785.html