如何通过SQL存储过程将指定XML的CHILDBATCH数据插入目标表？

1. 目标表结构定义 #

首先确保目标表已创建，结构匹配预期插入字段：

CREATE TABLE BatchInventory (
    NEW_BATCH_NUM VARCHAR(50),
    NEW_LOCATION VARCHAR(50),
    CHILD_BATCH_NUM VARCHAR(50),
    QTY INT
)

2. 编写存储过程 #

以下是解析XML并插入数据的存储过程（基于SQL Server实现）：

CREATE PROCEDURE InsertBatchData
    @InputXML XML
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO BatchInventory (NEW_BATCH_NUM, NEW_LOCATION, CHILD_BATCH_NUM, QTY)
    SELECT
        -- 提取根节点下的全局字段
        @InputXML.value('(/Root/NEW_BATCH_NUM)[1]', 'VARCHAR(50)') AS NEW_BATCH_NUM,
        @InputXML.value('(/Root/NEW_LOCATION)[1]', 'VARCHAR(50)') AS NEW_LOCATION,
        -- 遍历子批次节点，提取对应字段
        ChildBatch.value('(BATCH_NUM)[1]', 'VARCHAR(50)') AS CHILD_BATCH_NUM,
        ChildBatch.value('(QTY)[1]', 'INT') AS QTY
    FROM
        @InputXML.nodes('/Root/CHILD_BATCHES/CHILDBATCH') AS T(ChildBatch)
END

3. 代码逻辑说明 #

• .nodes() 方法：将XML中CHILDBATCH节点集合转换为行集，每个子节点对应一行待插入数据
• .value() 方法：从指定XML路径提取字段值，[1]确保取唯一值，避免返回多行结果
• 全局字段（NEW_BATCH_NUM、NEW_LOCATION）从根节点提取，与每个子批次的数据组合后批量插入表中

4. 调用存储过程示例 #

传入给定的XML数据执行存储过程：

DECLARE @XMLData XML = '
<Root>
    <NEW_BATCH_NUM>A1234</NEW_BATCH_NUM>
    <NEW_LOCATION>US11</NEW_LOCATION>
    <TRANSACTION_DATE>11/8/2024 6:54:03 AM</TRANSACTION_DATE>
    <CHILD_BATCHES>
        <CHILDBATCH>
            <BATCH_NUM>C101</BATCH_NUM>
            <QTY>1500</QTY>
        </CHILDBATCH>
        <CHILDBATCH>
            <BATCH_NUM>C102</BATCH_NUM>
            <QTY>2000</QTY>
        </CHILDBATCH>
    </CHILD_BATCHES>
</Root>'

EXEC InsertBatchData @InputXML = @XMLData

5. 验证插入结果 #

执行查询查看表中数据：

SELECT NEW_BATCH_NUM, NEW_LOCATION, CHILD_BATCH_NUM, QTY
FROM BatchInventory

返回结果与预期一致：

NEW_BATCH_NUM   NEW_LOCATION    CHILD_BATCH_NUM   QTY
------------------------------------------------------
A1234           US11            C101             1500
A1234           US11            C102             2000

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