如何将SQL Server中重复XML节点转换为JSON数组？

以下提供两种满足需求的实现方案，包含你要求的OpenXML方案，以及基于XQuery/xml.nodes()的方案：

示例XML数据 #

先定义包含重复<Item>节点的测试XML：

DECLARE @xml XML = N'
<Root>
  <Item>
    <ID>1</ID>
    <Name>Apple</Name>
  </Item>
  <Item>
    <ID>2</ID>
    <Name>Banana</Name>
  </Item>
  <Item>
    <ID>3</ID>
    <Name>Cherry</Name>
  </Item>
</Root>
'

方案一：使用OpenXML实现 #

利用OpenXML遍历重复节点，再通过STRING_AGG聚合生成JSON数组：

DECLARE @hdoc INT

-- 初始化XML文档句柄
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

-- 提取每个Item节点并转换为JSON数组
SELECT CONCAT('[', STRING_AGG(CONCAT('{"ID":', ID, ',"Name":"', STRING_ESCAPE(Name, 'json'), '"}'), ','), ']') AS ItemJsonArray
FROM OPENXML(@hdoc, '/Root/Item', 2)
WITH (
  ID INT 'ID',
  Name NVARCHAR(50) 'Name'
)

-- 释放XML文档句柄
EXEC sp_xml_removedocument @hdoc

说明：

• OPENXML的第三个参数2表示采用元素中心映射，直接绑定XML子节点到列
• STRING_ESCAPE用于处理JSON特殊字符（如引号、反斜杠），避免格式错误
• STRING_AGG将单个JSON对象拼接为完整数组

方案二：使用XQuery/xml.nodes()实现 #

兼容SQL Server 2016+版本 #

通过nodes()方法遍历重复节点，再聚合生成JSON：

SELECT CONCAT('[', STRING_AGG(CONCAT('{"ID":', i.value('(ID/text())[1]', 'INT'), ',"Name":"', STRING_ESCAPE(i.value('(Name/text())[1]', 'NVARCHAR(50)'), 'json'), '"}'), ','), ']') AS ItemJsonArray
FROM @xml.nodes('/Root/Item') AS t(i)

SQL Server 2022+简化版本 #

利用XQuery原生JSON构造函数直接生成数组：

SELECT @xml.query('
  array {
    for $i in /Root/Item
    return json-object(
      "ID": xs:int($i/ID/text()),
      "Name": xs:string($i/Name/text())
    )
  }
').value('.', 'NVARCHAR(MAX)') AS ItemJsonArray

说明：

• nodes()方法将XML拆分为单个<Item>节点的行集
• SQL Server 2022新增的array和json-object函数可直接生成标准JSON格式，无需手动拼接

内容的提问来源于stack exchange，提问作者mike_grinin