首页 > 资讯列表 > 编程/数据库 >> 数据库操作教程

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

数据库操作教程 2022-09-23 18:23:05 转载来源: 网络整理/侵权必删

实现SQLServer原生数据从XML生成JSON数据的实例代码  SQLServer是关系数据库,查询结果通常都是数据集,但是在一些特殊需求下,我们需要XML数据,最近这些年,JSON作为WebAPI常用的交换数据格式,那么数据库如何生成JSON数据呢?今天就写了一个DEMO.      1

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

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

       1.创建表及测试数据

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.查询结果集

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,那配置就会非常简单了,也能够大量减轻应用服务器的压力!

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

标签: 实现 SQLServer 原生 据从 XML 生成 JSON 数据 实例


声明:本文内容来源自网络,文字、图片等素材版权属于原作者,平台转载素材出于传递更多信息,文章内容仅供参考与学习,切勿作为商业目的使用。如果侵害了您的合法权益,请您及时与我们联系,我们会在第一时间进行处理!我们尊重版权,也致力于保护版权,站搜网感谢您的分享!

站长搜索

http://www.adminso.com

Copyright @ 2007~2024 All Rights Reserved.

Powered By 站长搜索

打开手机扫描上面的二维码打开手机版


使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

站长搜索目录系统技术支持