SQL Server插入同名嵌套XML节点数据至表时出现冗余空行如何解决

数据老陈

2026-9-3

问题描述

将XML中的数据插入数据表时，因XML结构存在根节点与子节点同名的情况，插入后表中出现多余的空值行。
原示例代码如下：

DECLARE @A TABLE
           (
               Id   INT IDENTITY(1,1) PRIMARY KEY,
               Var1 NVARCHAR(50),
               Var2 NVARCHAR(50)
           )

DECLARE @X XML = '<TEST SEGMENT="1">
                  <VAR1>1</VAR1>
                  <VAR2>2</VAR2>
                  <TEST SEGMENT="1">
                    <NAME>SomeName</NAME>
                    <ADDRESS>SomeAddress</ADDRESS>
                  </TEST>
                </TEST>'

INSERT INTO @A(Var1, Var2)
    SELECT 
        ISNULL(LTRIM(RTRIM(x.value('(VAR1)[1]/text()[1]', 'bigint'))), ''),
        ISNULL(LTRIM(RTRIM(x.value('(VAR2)[1]/text()[1]', 'nvarchar(50)'))), '')
    FROM 
        @x.nodes('//TEST') o(x)

SELECT * FROM @A

执行上述代码后会返回2行数据，其中1行的Var1、Var2字段为空值。

问题原因

原代码中使用XPath路径//TEST做节点匹配，该语法会递归查找XML文档中所有层级的名为TEST的节点，既会匹配外层包含VAR1、VAR2字段的根TEST节点，也会匹配内层仅包含NAME、ADDRESS字段的子TEST节点。内层TEST节点下不存在VAR1、VAR2子节点，取值时自然返回空值，最终导致表中插入多余的空行。

解决方案

核心是调整XPath匹配规则，精准定位到需要提取数据的目标TEST节点，避免匹配到无对应字段的同名节点，根据业务场景可选择以下两种改法：

场景1：仅需提取最外层根TEST节点的数据
将nodes方法中的XPath从//TEST改为/TEST，该写法仅匹配XML根层级的TEST节点，不会递归查找内层嵌套的同名节点。修改后的完整代码如下：

DECLARE @A TABLE
           (
               Id   INT IDENTITY(1,1) PRIMARY KEY,
               Var1 NVARCHAR(50),
               Var2 NVARCHAR(50)
           )

DECLARE @X XML = '<TEST SEGMENT="1">
                  <VAR1>1</VAR1>
                  <VAR2>2</VAR2>
                  <TEST SEGMENT="1">
                    <NAME>SomeName</NAME>
                    <ADDRESS>SomeAddress</ADDRESS>
                  </TEST>
                </TEST>'

INSERT INTO @A(Var1, Var2)
    SELECT 
        ISNULL(LTRIM(RTRIM(x.value('(VAR1)[1]/text()[1]', 'nvarchar(50)'))), ''),
        ISNULL(LTRIM(RTRIM(x.value('(VAR2)[1]/text()[1]', 'nvarchar(50)'))), '')
    FROM 
        @x.nodes('/TEST') o(x)

SELECT * FROM @A

执行后仅会插入1行正确数据：Var1值为1，Var2值为2，无多余空行。

场景2：需要提取所有包含VAR1、VAR2字段的TEST节点（适配多层嵌套的XML结构）
若XML存在多层嵌套的同名TEST节点，仅需提取带目标字段的节点，可以给XPath增加节点存在性判断，过滤掉无对应字段的TEST节点，将nodes方法的路径改为//TEST[VAR1 and VAR2]即可，该写法会递归查找所有TEST节点，但仅保留同时存在VAR1、VAR2子节点的条目，从根源避免空值行。

注：原代码中VAR1字段取值时转换为bigint类型后再插入NVARCHAR(50)类型的字段属于冗余类型转换，上述示例已统一调整为nvarchar(50)类型，避免不必要的类型转换开销。

内容的提问来源于stack exchange，提问作者N.Tesla