SQL Server导入XML仅返回单条记录，需一次性获取全部数据

我在使用SQL Server导入XML数据时遇到问题：当前查询仅返回一条记录，我需要一次性获取所有记录。

XML示例代码 #

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = 
N'<ROOT>
   <B2A>
    <B2A.01>
     <B2A.01.1>00</B2A.01.1>
    </B2A.01>
    <B2A.02>
     <B2A.02.1>LT</B2A.02.1>
    </B2A.02>
   </B2A>
   <L11>
    <L11.01>
     <L11.01.1>1123</L11.01.1>
    </L11.01>
   </L11>
   <L11>
    <L11.01>
     <L11.01.1>120001502140</L11.01.1>
    </L11.01>
    <L11.02>
     <L11.02.1>BN</L11.02.1>
    </L11.02>
   </L11>
   <L11>
    <L11.01>
     <L11.01.1>KDC</L11.01.1>
    </L11.01>
    <L11.02>
     <L11.02.1>11</L11.02.1>
    </L11.02>
    <L11.03>
     <L11.03.1>11</L11.03.1>
    </L11.03>
   </L11>
 </ROOT>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

我的查询语句 #

SELECT 
T1.be.value ('(B2A/B2A.01/B2A.01.1)[1]' ,'nvarchar(4)')  as [tcode],
T1.be.value ('(B2A/B2A.02/B2A.02.1)[1]' ,'nvarchar(4)')  as [apptype],
T.te.value ('(L11/L11.01/L11.01.1)[1]' ,'nvarchar(60)')  as [id],
T.te.value ('(L11/L11.02/L11.02.1)[1]' ,'nvarchar(6)')   as [Ref id],
T.te.value ('(L11/L11.03/L11.03.1)[1]' ,'nvarchar(160)') as [DESCRIPTION]
FROM    (
    SELECT  @xml AS x
    ) xml  
x.nodes('/ROOT/L11') T(te)
outer apply x.nodes('/ROOT/B2A') T1(be)

当前返回结果 #

期望返回结果 #

解决方案 #

你的查询存在两个核心问题：

1. 路径写法错误：T.te已经指向<L11>节点，在value()函数里不需要再添加L11/前缀，否则会从根节点重新查找，导致无法匹配到当前<L11>下的子节点，返回null。
2. 不必要的交叉应用：<B2A>节点在XML中只有一个，不需要和每个<L11>节点做outer apply，直接从根节点读取即可。

修改后的查询语句（简洁版）：

SELECT 
    -- 直接从ROOT节点读取唯一的B2A数据
    @XML.value('(/ROOT/B2A/B2A.01/B2A.01.1)[1]', 'nvarchar(4)') AS [tcode],
    @XML.value('(/ROOT/B2A/B2A.02/B2A.02.1)[1]', 'nvarchar(4)') AS [apptype],
    -- 基于当前L11节点读取子节点数据
    T.te.value('(L11.01/L11.01.1)[1]', 'nvarchar(60)') AS [id],
    T.te.value('(L11.02/L11.02.1)[1]', 'nvarchar(6)') AS [Ref id],
    T.te.value('(L11.03/L11.03.1)[1]', 'nvarchar(160)') AS [DESCRIPTION]
FROM @XML.nodes('/ROOT/L11') T(te)

如果需要用apply方式拆分逻辑，也可以这样写：

SELECT 
    B2AData.tcode,
    B2AData.apptype,
    T.te.value('(L11.01/L11.01.1)[1]', 'nvarchar(60)') AS [id],
    T.te.value('(L11.02/L11.02.1)[1]', 'nvarchar(6)') AS [Ref id],
    T.te.value('(L11.03/L11.03.1)[1]', 'nvarchar(160)') AS [DESCRIPTION]
FROM @XML.nodes('/ROOT/L11') T(te)
CROSS APPLY (
    SELECT 
        @XML.value('(/ROOT/B2A/B2A.01/B2A.01.1)[1]', 'nvarchar(4)') AS tcode,
        @XML.value('(/ROOT/B2A/B2A.02/B2A.02.1)[1]', 'nvarchar(4)') AS apptype
) B2AData

两种写法都能返回你期望的3条记录，且字段值完全匹配需求。

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