SQL 中的嵌套 XML 转换为 JSON
4.81/5 (11投票s)
将 SQL Server 的嵌套 XML 数据获取为 JSON 字符串
引言
本文介绍如何将 SQL Server 中的数据输出为 JSON 字符串。它遵循 Phil Factor 在 https://www.simple-talk.com/sql/t-sql-programming/producing-json-documents-from-sql-server-queries-via-tsql 提供的路径，但采用了一种不同的（我认为更简单）方法。
背景
在 SQL Server 中，从 SQL 转换为 XML 非常直接，SQL Server 本身就直接支持。然而，转换为 JSON 是一个不同的问题，因为没有直接的 JSON 支持，唯一的选择是生成一个包含所需数据的 NVARCHAR。
从 XML 节点生成简单的（无嵌套对象）JSON 对象非常容易，只需使用 Xml 数据类型方法：local-name()、text()，进行一些类型转换和转义，将所有内容连接成一个 NVARCHAR，就完成了。然而，对于嵌套对象，问题会变得棘手一些，因为何时停止连接就变得至关重要。
何时停止连接取决于你从哪里开始连接。如果你从根节点开始，那么你应该在进入叶节点时停止。或者，如果你从叶节点开始，那么你必须在到达根节点时停止。
Phil 的提议遵循后者方向，它解析 XML，构建一个层次结构表，识别叶节点，将它们转换为 JSON 字符串，然后是它们的父节点，然后是它们的父节点的父节点……一直持续到根节点。这是一个使用自下而上方法的通用解决方案。在实际情况中，XML 解析可能会被省略，因为使用 SQL 或 XPath 查询可以更有效地填充层次结构表。
那么自顶向下呢？起始节点（即根节点）是已知的，叶节点是未知的，但识别它们很简单：它们是没有子节点的节点。
生成我们的 XML
我们知道我们需要一些具有一定嵌套级别的 XML 数据。既然我们在 SQL 世界中，不如从一些表中生成。让我们为气象服务创建一个示例数据库。我们的服务有数据收集站。每个站点收集月度温度和降雨统计数据，每个统计数据由一个或多个操作员审查。为了使其更现实，让我们添加一些 null 值，但有一些统计数据未被审查。
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)
让我们熟悉一下我们的数据
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
ID City State LatN LongW Month Rain Temp Name Surname
--------------------------------------------------------------------------
13 Phoenix AZ 33 112 1 0.31 57.4 John "The Fox" Brown
13 Phoenix AZ 33 112 7 5.15 91.7 John "The Fox" Brown
44 Denver CO 40 105 1 0.18 27.3 {null} {null}
44 Denver CO 40 105 7 2.11 74.8 Paul Smith
44 Denver CO 40 105 7 2.11 74.8 Michael Williams
44 Denver CO 40 105 7 2.11 74.8 John "The Fox" Brown
66 Caribou ME 47 68 1 2.1 6.7 Paul Smith
66 Caribou ME 47 68 7 4.52 65.8 Paul Smith
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>
...
...
</station>
转换为 JSON
为了将 XML 节点转换为 JSON，我们将使用 Xml 数据类型方法
- 为了获取 XML 的节点，我们将选择
.nodes(Xpath_Expression) 的结果，该函数将匹配给定 Xpath_Expression 的每个节点作为一行，其中只有一列是 XML 类型。这些 XML 行将被“选择”，在提取它们的 JSON 名称和 JSON 值后，将构成每个 JSON 实体的属性。
- 为了从 XML 行获取 JSON 属性名，我们将使用
.value('local-name(.)', 'NVARCHAR(255)')获取节点的本地名称作为NVARCHAR。
- 同样，为了从 XML 行获取 JSON 值，我们将使用
.value('text()[1]','NVARCHAR(MAX)')，该函数返回 XML 文本作为 NVARCHAR，之后会进行格式化。
这里是新的部分
- 我们可以通过测试一个节点是否有零个子节点
.value('count(*)','int')=0来判断它是否是叶节点。如果一个节点没有子节点，那么它的内容就是一个简单的 JSON 值，它可以是字符串或数字（因为 XML 中没有 null 值）。在 JSON 表示方面，唯一的区别是引号和字符转义。这种表示由dbo.qfn_JsonEscape处理。
- 如果一个 XML 节点有一个或多个子节点，那么我们将通过执行
.query('*')来获取它的子节点作为 XML 子树，然后我们可以再次使用 XML 节点到 JSON 函数。
也就是说，我们引入了递归来遵循 XML 层次结构。每次调用该函数，都会：
- 处理一个无子节点的 XML 子树，通过简单地使用 Xml 数据类型方法和简单的字符串连接，可以将其转换为 JSON 字符串。此过程非常直接，只需连接：
{,}符号{"}符号- 节点
local-name()或空字符串（如果为 null）（注意 coalesce 函数） - 再次是
{"}符号 {;}符号- 将节点值应用
dbo.qfn_JsonEscape的结果
- 或者处理一个 XML 子树，它将连接数组字符（
[ ]）并使用子树作为新参数调用函数。
整合所有内容
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
最后一步是，一旦整个集合被连接和处理，SQL 的 stuff 函数会删除数组中的第一个逗号（,）符号。
帮助函数 dbo.[qfn_JsonEscape] 只是
-
将任何非数字值视为字符串，用双引号括起来，并对控制字符进行 JSON 转义。
-
返回接收到的任何数字值（不加引号）
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
测试
我们可以通过执行以下命令来测试结果
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"}]}]}]
这是预期的结果（注意 John “The Fox” 引号的转义）。你可以在 http://sqlfiddle.com/#!6/77909/1 进行测试。
结论
在 XML 和 JSON 中使用递归是“自然而然”的，尽管这在 TSQL 中通常不是这样，但它允许直接而优雅的解决方案。像任何基于递归的解决方案一样，它在性能方面是有代价的，所以我建议不要将此技术用于返回超过一千条记录的查询。