T-SQL使用OPENXML读取XML节点仅首行有值，如何修正路径？

原问题分析 #

你使用OPENXML读取XML中的projectId节点时，仅第一行返回有效值，其余行均为NULL，核心原因是XPath路径指定错误：原代码中OPENXML的第二个参数为'ROOT//'，该路径会将<ROOT>节点作为父容器，把它的所有直接子元素（即所有<projectId>）视为同一行的不同列，但你的WITH子句只定义了projectId列，因此仅能获取第一个<projectId>的值，后续节点因无对应列映射返回NULL。

修正后的OPENXML代码 #

将XPath路径改为指向每个<projectId>节点，让每个节点单独成为一行，即可获取所有值：

DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(4000);
SET @XmlDocument = N'<ROOT>
<projectId>15468</projectId><projectId>15469</projectId><projectId>15472</projectId> 
<projectId>15849</projectId><projectId>13801</projectId><projectId>13802</projectId>
<projectId>13803</projectId><projectId>15684</projectId><projectId>14044</projectId> 
<projectId>15722</projectId><projectId>15753</projectId><projectId>15770</projectId> 
<projectId>15771</projectId>
</ROOT>';
-- 创建XML文档的内部表示
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
-- 使用OPENXML行集提供器执行查询
SELECT *
FROM OPENXML (@DocHandle, 'ROOT/projectId', 2) -- 修改XPath定位到每个projectId节点
  WITH (projectId  VARCHAR(10) '.'); -- '.' 表示取当前节点的文本值

-- 必须释放XML文档句柄，避免内存泄漏
EXEC sp_xml_removedocument @DocHandle;

关键修改点 #

• XPath路径改为'ROOT/projectId'：直接定位到<ROOT>下的每个<projectId>节点，每个节点对应结果集中的一行。
• WITH子句中projectId指定路径为'.'：明确取当前节点（即<projectId>）的文本值，也可省略'.'（因为模式2为元素中心，列名与节点名匹配时会自动映射文本）。
• 新增sp_xml_removedocument调用：原代码遗漏了该步骤，会导致SQL Server内存泄漏，必须在使用完OPENXML后释放文档句柄。

关于你实际业务中使用的nodes方法代码 #

你基于nodes()方法的实现是正确且推荐的：

• nodes('//projectId')会将XML中所有<projectId>节点拆分为行集，每个节点对应一行。
• value('.', 'int')取当前节点的文本值并转换为int类型，插入临时表的逻辑无误。

另外可以优化的点：无需单独判断count(//projectId) > 0，如果没有匹配节点，INSERT...SELECT不会插入任何数据，可简化代码：

SET NOCOUNT ON;

create table #selectedProjectsList (projectId int)
create index #idx_selectedProjectsList On #selectedProjectsList (projectId)

if @params is not null
BEGIN
  insert into #selectedProjectsList
  select PIDS.PID.value('.', 'int')
  From @params.nodes('//projectId') as PIDS(PID)
END

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