SQL Server中循环遍历XML元素按序获取节点值的实现方法

需要在SQL Server中遍历传入XML结构内的所有<PersonelsIdsVm>节点，逐个提取PersonelId值，在循环中执行自定义业务逻辑。初始提供的代码缺少总节点数统计、按索引取节点值的核心逻辑，也未做XML文档句柄的释放处理，存在内存泄漏风险。

完全兼容已写的sp_xml_preparedocument初始化逻辑，补全后可直接运行：

DECLARE @PersonelIds XML
    = '<?xml version="1.0" encoding="utf-8"?>
       <ArrayOfPersonelsIdsVm xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
           <PersonelsIdsVm>
               <PersonelId>c2aadd5d-209d-ec11-8f3c-b42e99ed152b</PersonelId>
           </PersonelsIdsVm>
           <PersonelsIdsVm>
               <PersonelId>0d197668-209d-ec11-8f3c-b42e99ed152b</PersonelId>
           </PersonelsIdsVm>
       </ArrayOfPersonelsIdsVm>';

DECLARE @FromDate DATE = CONVERT(DATE, '2022/05/1');
DECLARE @Days INT = 0;

-- XML句柄初始化
DECLARE @handler INT;
EXEC sys.sp_xml_preparedocument @handler OUT, @PersonelIds;

-- 统计节点总数
DECLARE @TotalCount INT;
SELECT @TotalCount = COUNT(*)
FROM OPENXML(@handler, '/ArrayOfPersonelsIdsVm/PersonelsIdsVm', 2)
WITH (PersonelId UNIQUEIDENTIFIER 'PersonelId');

DECLARE @i INT = 1; -- XPath位置索引从1开始计数
DECLARE @CurrentPersonelId UNIQUEIDENTIFIER;

WHILE @i <= @TotalCount
BEGIN
    -- 按索引提取当前循环对应的PersonelId
    SELECT @CurrentPersonelId = PersonelId
    FROM OPENXML(@handler, '/ArrayOfPersonelsIdsVm/PersonelsIdsVm', 2)
    WITH (
        NodeSeq INT '@mp:id', -- 读取节点内置元属性保证顺序和原XML一致
        PersonelId UNIQUEIDENTIFIER 'PersonelId'
    )
    WHERE NodeSeq = @i;

    -- 此处编写自定义业务逻辑，示例为打印当前提取到的ID
    PRINT @CurrentPersonelId;

    SET @i = @i + 1;
END

-- 必须释放XML文档句柄，否则会持续占用MSXML解析器内存
EXEC sys.sp_xml_removedocument @handler;

SQL Server 2005及以上版本支持原生XQuery方法，无需手动初始化/释放句柄，语法更简洁、执行性能更好：

DECLARE @PersonelIds XML
    = '<?xml version="1.0" encoding="utf-8"?>
       <ArrayOfPersonelsIdsVm xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
           <PersonelsIdsVm>
               <PersonelId>c2aadd5d-209d-ec11-8f3c-b42e99ed152b</PersonelId>
           </PersonelsIdsVm>
           <PersonelsIdsVm>
               <PersonelId>0d197668-209d-ec11-8f3c-b42e99ed152b</PersonelId>
           </PersonelsIdsVm>
       </ArrayOfPersonelsIdsVm>';

DECLARE @FromDate DATE = CONVERT(DATE, '2022/05/1');
DECLARE @Days INT = 0;

-- 按XML原有顺序把所有PersonelId存入临时表，生成行号供循环使用
SELECT 
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNum,
    PId.value('(PersonelId/text())[1]', 'UNIQUEIDENTIFIER') AS PersonelId
INTO #PersonelList
FROM @PersonelIds.nodes('/ArrayOfPersonelsIdsVm/PersonelsIdsVm') AS T(PId);

DECLARE @TotalCount INT = @@ROWCOUNT;
DECLARE @i INT = 1;
DECLARE @CurrentPersonelId UNIQUEIDENTIFIER;

WHILE @i <= @TotalCount
BEGIN
    SELECT @CurrentPersonelId = PersonelId
    FROM #PersonelList
    WHERE RowNum = @i;

    -- 此处编写自定义业务逻辑
    PRINT @CurrentPersonelId;

    SET @i = @i + 1;
END

DROP TABLE IF EXISTS #PersonelList;

注意事项 #

• 如果业务逻辑不需要严格逐行按顺序执行，优先用集合操作关联XML解析结果集直接处理，性能远高于循环写法
• 使用OpenXML方案时切记最后释放句柄，SQL Server不会自动回收这部分内存
• XML相关的索引计数均从1开始，不要按常规编程习惯设初始值为0，会导致漏取第一个节点

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