自己写的sql返回json格式数据(sqlserver保存json并解析)

技术实现SQL Server 原生数据从XML生成JSON数据的实例代码怎么编写实现SQL Server 原生数据从XML生成JSON数据的实例代码怎么编写,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为

实现结构化查询语言服务器原生数据从可扩展标记语言生成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

(0)

相关推荐

  • 抖音刷赞软件网站,下单平台业务秒刷网?

    技术抖音刷赞软件网站,下单平台业务秒刷网?对抖音创始人来说,不断更新高质量的原创作品是非常必要的。早期的短视频作品通过短视频吸引了大量的粉丝关注,其最终目的是使粉丝量不断增长,再通过短视频的带货或直播的带货来获取一定的收

    测评 2021年10月20日
  • Windows Java代码如何远程访问HBase

    技术Windows Java代码如何远程访问HBase这篇文章将为大家详细讲解有关Windows Java代码如何远程访问HBase,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定

    攻略 2021年11月24日
  • 常见html5语义化标签有哪些

    技术常见html5语义化标签有哪些这篇文章主要介绍“常见html5语义化标签有哪些”,在日常操作中,相信很多人在常见html5语义化标签有哪些问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”

    攻略 2021年11月6日
  • Qt中树形控件Tree Widget的使用方法有哪些

    技术Qt中树形控件Tree Widget的使用方法有哪些本篇内容主要讲解“Qt中树形控件Tree Widget的使用方法有哪些”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“Q

    攻略 2021年11月30日
  • 将文件压缩成PNG图像存储方法是什么

    技术将文件压缩成PNG图像存储方法是什么本篇内容介绍了“将文件压缩成PNG图像存储方法是什么”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅

    攻略 2021年11月18日
  • 投入的近义词,“尽心尽力”类似的词语有哪些

    技术投入的近义词,“尽心尽力”类似的词语有哪些竭尽全力[jiéjìnquánlì]?:形容用尽全部力量投入的近义词。
    尽心尽力[jìnxīnjìnlì]:做事非常的卖力,或为非常的卖命,以用尽了自己所有的能力。
    尽心尽责

    生活 2021年10月30日