TSQL中遍历XML子节点插入数据：有无比游标更简便的方案？

当然有，用数据库原生的XML批量解析功能就能实现，比游标高效得多。这类方案属于基于集合的操作，避免了游标逐行处理的性能损耗，尤其适合批量数据插入场景。

以下以主流数据库为例给出实现代码：

SQL Server 实现 #

首先假设你的Orders表结构如下：

CREATE TABLE Orders (
    ItemCode VARCHAR(20),
    ProductName VARCHAR(100)
);

直接通过nodes()和value()函数配合INSERT...SELECT完成批量插入：

DECLARE @XmlData XML = '
<XMLGateway>
  <Header>
    ....
  </Header>
  <Body>
    <Orders>
      <Order>
        <ItemCode>315689</ItemCode>
        <ProductName>Item1</ProductName>
      </Order>
      <Order>
        <ItemCode>123456</ItemCode>
        <ProductName>Product 1</ProductName>
      </Order>
    </Orders>
  </Body>
</XMLGateway>';

INSERT INTO Orders (ItemCode, ProductName)
SELECT
    -- 提取ItemCode节点的文本值，[1]确保只取第一个匹配元素
    OrderNode.value('(ItemCode/text())[1]', 'VARCHAR(20)') AS ItemCode,
    -- 提取ProductName节点的文本值
    OrderNode.value('(ProductName/text())[1]', 'VARCHAR(100)') AS ProductName
-- 定位所有Order节点，生成临时行集
FROM @XmlData.nodes('/XMLGateway/Body/Orders/Order') AS XmlData(OrderNode);

MySQL 8.0+ 实现 #

使用XMLTABLE函数解析XML并插入：

SET @XmlData = '<XMLGateway>
  <Header>
    ....
  </Header>
  <Body>
    <Orders>
      <Order>
        <ItemCode>315689</ItemCode>
        <ProductName>Item1</ProductName>
      </Order>
      <Order>
        <ItemCode>123456</ItemCode>
        <ProductName>Product 1</ProductName>
      </Order>
    </Orders>
  </Body>
</XMLGateway>';

INSERT INTO Orders (ItemCode, ProductName)
SELECT ItemCode, ProductName
FROM XMLTABLE(
    '/XMLGateway/Body/Orders/Order'
    PASSING @XmlData
    COLUMNS
        ItemCode VARCHAR(20) PATH 'ItemCode',
        ProductName VARCHAR(100) PATH 'ProductName'
);

Oracle 实现 #

借助XMLTYPE和XMLTABLE完成解析插入：

DECLARE
  v_xml_data XMLTYPE := XMLTYPE('
<XMLGateway>
  <Header>
    ....
  </Header>
  <Body>
    <Orders>
      <Order>
        <ItemCode>315689</ItemCode>
        <ProductName>Item1</ProductName>
      </Order>
      <Order>
        <ItemCode>123456</ItemCode>
        <ProductName>Product 1</ProductName>
      </Order>
    </Orders>
  </Body>
</XMLGateway>');
BEGIN
  INSERT INTO Orders (ItemCode, ProductName)
  SELECT ItemCode, ProductName
  FROM XMLTABLE(
      '/XMLGateway/Body/Orders/Order'
      PASSING v_xml_data
      COLUMNS
          ItemCode VARCHAR2(20) PATH 'ItemCode',
          ProductName VARCHAR2(100) PATH 'ProductName'
  );
  COMMIT;
END;
/

核心优势 #

• 性能远高于游标：基于集合的操作是数据库优化的重点，避免了游标循环的上下文切换开销。
• 代码更简洁：无需定义游标、循环逻辑、处理变量赋值等冗余代码。
• 维护成本低：逻辑清晰，便于后续修改或扩展。

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