实现SQL Server 原生数据从XML生成JSON数据的实例代码怎么编写，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

实现SQL Server 原生数据从XML生成JSON数据的实例代码

SQL Server 是关系数据库，查询结果通常都是数据集，但是在一些特殊需求下，我们需要XML数据，最近这些年，JSON作为WebAPI常用的交换数据格式，那么数据库如何生成JSON数据呢？今天就写了一个DEMO.

1.创建表及测试数据

SETNOCOUNTONIFOBJECT_ID('STATS')ISNOTNULLDROPTABLESTATSIFOBJECT_ID('STATIONS')ISNOTNULLDROPTABLESTATIONSIFOBJECT_ID('OPERATORS')ISNOTNULLDROPTABLEOPERATORSIFOBJECT_ID('REVIEWS')ISNOTNULLDROPTABLEREVIEWS--CreateandpopulatetablewithStationCREATETABLESTATIONS(IDINTEGERPRIMARYKEY,CITYNVARCHAR(20),STATECHAR(2),LAT_NREAL,LONG_WREAL);INSERTINTOSTATIONSVALUES(13,'Phoenix','AZ',33,112);INSERTINTOSTATIONSVALUES(44,'Denver','CO',40,105);INSERTINTOSTATIONSVALUES(66,'Caribou','ME',47,68);--CreateandpopulatetablewithOperatorsCREATETABLEOPERATORS(IDINTEGERPRIMARYKEY,NAMENVARCHAR(20),SURNAMENVARCHAR(20));INSERTINTOOPERATORSVALUES(50,'John"TheFox"','Brown');INSERTINTOOPERATORSVALUES(51,'Paul','Smith');INSERTINTOOPERATORSVALUES(52,'Michael','Williams');--CreateandpopulatetablewithnormalizedtemperatureandprecipitationdataCREATETABLESTATS(STATION_IDINTEGERREFERENCESSTATIONS(ID),MONTHINTEGERCHECK(MONTHBETWEEN1AND12),TEMP_FREALCHECK(TEMP_FBETWEEN-80AND150),RAIN_IREALCHECK(RAIN_IBETWEEN0AND100),PRIMARYKEY(STATION_ID,MONTH));INSERTINTOSTATSVALUES(13,1,57.4,0.31);INSERTINTOSTATSVALUES(13,7,91.7,5.15);INSERTINTOSTATSVALUES(44,1,27.3,0.18);INSERTINTOSTATSVALUES(44,7,74.8,2.11);INSERTINTOSTATSVALUES(66,1,6.7,2.10);INSERTINTOSTATSVALUES(66,7,65.8,4.52);--CreateandpopulatetablewithReviewCREATETABLEREVIEWS(STATION_IDINTEGER,STAT_MONTHINTEGER,OPERATOR_IDINTEGER)insertintoREVIEWSVALUES(13,1,50)insertintoREVIEWSVALUES(13,7,50)insertintoREVIEWSVALUES(44,7,51)insertintoREVIEWSVALUES(44,7,52)insertintoREVIEWSVALUES(44,7,50)insertintoREVIEWSVALUES(66,1,51)insertintoREVIEWSVALUES(66,7,51)

2.查询结果集

selectSTATIONS.IDasID,STATIONS.CITYasCity,STATIONS.STATEasState,STATIONS.LAT_NasLatN,STATIONS.LONG_WasLongW,STATS.MONTHasMonth,STATS.RAIN_IasRain,STATS.TEMP_FasTemp,OPERATORS.NAMEasName,OPERATORS.SURNAMEasSurnamefromstationsinnerjoinstatsonstats.STATION_ID=STATIONS.IDleftjoinreviewsonreviews.STATION_ID=stations.idandreviews.STAT_MONTH=STATS.[MONTH]leftjoinOPERATORSonOPERATORS.ID=reviews.OPERATOR_ID

结果：

2.查询xml数据

selectstations.*,(selectstats.*,(selectOPERATORS.*fromOPERATORSinnerjoinreviewsonOPERATORS.ID=reviews.OPERATOR_IDwherereviews.STATION_ID=STATS.STATION_IDandreviews.STAT_MONTH=STATS.MONTHforxmlpath('operator'),type)operatorsfromSTATSwhereSTATS.STATION_ID=stations.IDforxmlpath('stat'),type)statsfromstationsforxmlpath('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"TheFox"</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"TheFox"</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"TheFox"</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）创建辅助函数

CREATEFUNCTION[dbo].[qfn_XmlToJson](@XmlDataxml)RETURNSnvarchar(max)ASBEGINdeclare@mnvarchar(max)SELECT@m='['+Stuff((SELECTthelinefrom(SELECT','+'{'+Stuff((SELECT',"'+coalesce(b.c.value('local-name(.)','NVARCHAR(255)'),'')+'":'+casewhenb.c.value('count(*)','int')=0thendbo.[qfn_JsonEscape](b.c.value('text()[1]','NVARCHAR(MAX)'))elsedbo.qfn_XmlToJson(b.c.query('*'))endfromx.a.nodes('*')b(c)forxmlpath(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')+'}'from@XmlData.nodes('/*')x(a))JSON(theLine)forxmlpath(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')+']'return@mEND

CREATEFUNCTION[dbo].[qfn_JsonEscape](@valuenvarchar(max))returnsnvarchar(max)asbeginif(@valueisnull)return'null'if(TRY_PARSE(@valueasfloat)isnotnull)return@valueset@value=replace(@value,'\','\\')set@value=replace(@value,'"','\"')return'"'+@value+'"'end

3）查询sql

selectdbo.qfn_XmlToJson((selectstations.ID,stations.CITY,stations.STATE,stations.LAT_N,stations.LONG_W,(selectstats.*,(selectOPERATORS.*fromOPERATORSinnerjoinreviewsonOPERATORS.ID=reviews.OPERATOR_IDwherereviews.STATION_ID=STATS.STATION_IDandreviews.STAT_MONTH=STATS.MONTHforxmlpath('operator'),type)operatorsfromSTATSwhereSTATS.STATION_ID=stations.IDforxmlpath('stat'),type)statsfromstationsforxmlpath('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\"TheFox\"","SURNAME":"Brown"}]},{"STATION_ID":13,"MONTH":7,"TEMP_F":9.1699997e+001,"RAIN_I":5.1500001e+000,"operators":[{"ID":50,"NAME":"John\"TheFox\"","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\"TheFox\"","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，那配置就会非常简单了，也能够大量减轻应用服务器的压力！

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。