SQL Server中如何将CustomerOrder表的XML列数据导出为结构化表

1. 创建目标结构化存储表 #

先按照你期望的输出格式创建规范的明细存储表，字段类型和原表逻辑匹配：

CREATE TABLE [dbo].[CustomerOrderDetail]
(
    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [CustomerOrderId] INT NOT NULL,
    [Name] NVARCHAR(30) NOT NULL,
    [OrderDate] DATETIME2 NOT NULL,
    [ItemName] NVARCHAR(100) NOT NULL,
    [Quantity] INT NOT NULL,
    [Rate] DECIMAL(18,2) NOT NULL,
    [NetAmount] DECIMAL(18,2) NOT NULL
)

2. 解析XML并导入数据 #

执行以下SQL即可完成XML字段解析、明细拆分和数据写入目标表：

INSERT INTO [dbo].[CustomerOrderDetail]
(
    [CustomerOrderId],
    [Name],
    [OrderDate],
    [ItemName],
    [Quantity],
    [Rate],
    [NetAmount]
)
SELECT
    co.Id AS CustomerOrderId,
    co.CustomerName AS Name,
    co.OrderDate,
    T.c.value('(Name/text())[1]', 'NVARCHAR(100)') AS ItemName,
    T.c.value('(Quantity/text())[1]', 'INT') AS Quantity,
    T.c.value('(Rate/text())[1]', 'DECIMAL(18,2)') AS Rate,
    T.c.value('(NetAmount/text())[1]', 'DECIMAL(18,2)') AS NetAmount
FROM [dbo].[CustomerOrder] co
CROSS APPLY co.Items.nodes('/Orders/Order/Item') T(c)

3. 验证结果 #

执行以下查询即可查看导入后的结构化数据，和你期望的输出格式完全一致：

SELECT * FROM [dbo].[CustomerOrderDetail]

核心逻辑说明 #

• nodes('/Orders/Order/Item') T(c) 方法会将每个XML中的<Item>节点拆分为独立的行，实现单条订单记录对应多条明细的行展开
• value() 方法通过XPath路径读取对应节点的文本内容，并转换为指定的SQL数据类型
• CROSS APPLY 是SQL Server中用于关联表值函数返回结果的语法，这里用于关联原订单表和拆分后的XML节点行

内容的提问来源于stack exchange，提问作者B.Balamanigandan