Shulou(Shulou.com)05/31 Report--

To achieve SQL Server native data from XML to generate JSON data example code how to write, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

An example code for generating SQL Server data from XML to JSON native data

SQL Server is a relational database, and the query results are usually datasets, but under some special requirements, we need XML data. In recent years, JSON is a common exchange data format for WebAPI, so how does the database generate JSON data? I wrote a DEMO today.

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', 33112) 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 (13pint 1, 50) insert into REVIEWS VALUES (13, 7, 50) insert into REVIEWS VALUES (44, 7, 51) insert into REVIEWS VALUES (4, 4, 7, 52) insert into REVIEWS VALUES (44, 7, 50) insert into REVIEWS VALUES (66, 1, 51) insert into REVIEWS VALUES (6, 6, 7, 51)

two。 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

Results:

two。 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

Results:

13 Phoenix AZ 3.3000000e+001 1.1200000e+002 13 1 5.7400002e+001 3.1000000e-001 50 John "The Fox" Brown 13 7 9.1699997e+001 5.1500001e+000 50 John "The Fox" Brown 44 Denver CO 4.0000000e+001 1.0500000e+002 44 1 2.7299999e+001 1.8000001e-001 44 7 7.4800003e+001 2.1099999e+000 51 Paul Smith 52 Michael Williams 50 John "The Fox" Brown 66 Caribou ME 4.7000000e+001 6.8000000e+001 66 1 6.6999998e+000 2.0999999e+000 51 Paul Smith 66 7 6 .5800003e + 001 4.5200000e+000 51 Paul Smith

3. How to generate JSON data

1) create an auxiliary function

CREATE FUNCTION [dbo]. [qfn_XmlToJson] (@ XmlData xml) RETURNS nvarchar (max) AS BEGIN declare @ m nvarchar (max) SELECT @ m Stuff'['+ 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) from @ XmlData.nodes ('/ *') x (a) JSON (theLine) for xml path (''), TYPE) .value ('.', 'NVARCHAR (MAX)'), 1jue 1 '') +'] 'return @ m ENDCREATE 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))

Results:

[{"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 as a flexible Web communication exchange architecture, if the configuration data is stored in the database, directly access the JSON, then the configuration will be very simple, but also can greatly reduce the pressure on the application server!

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.