Implementation of SQL Server native data from XML to generate JSON data instance code, xmljson

Implementation of SQL Server native data from XML to generate JSON data instance code, xmljson

Example code for generating JSON data from XML with SQL Server native data

SQL Server is a relational database, and the query results are usually datasets. However, in some special situations, we need XML data. In the past few years, JSON has been used as a common data interchange format for webapis, how does the database generate JSON data? Today I wrote a DEMO.

1. Create tables and test data

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. query result set

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 

Result:

2. query xml data

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 

Result:

<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. How to generate JSON data

1) create auxiliary functions

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) query 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   ) ) 

Result:

[ {"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"}]}]}] 

Summary:

JSON is a flexible Web communication and exchange architecture. If configuration data is stored in the database and JSON is directly obtained, the configuration is very simple and can greatly reduce the pressure on the application server!

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!