SQL Server中XML列多节点转列查询技术问询

没问题！针对你这种SQL Server里XML列提取不定数量节点的需求，我给你整理了两种实用方法，都能轻松搞定：

这是SQL Server处理XML最常用的方式，nodes()方法会把XML中指定路径的每个节点拆分成单独的行，再配合value()提取属性值。如果想保留那些person节点数量为0的行，把CROSS APPLY换成OUTER APPLY就行。

SELECT 
    t.RowID,
    p.person_node.value('@Name', 'nvarchar(100)') AS Name,
    p.person_node.value('@Lastname', 'nvarchar(100)') AS Lastname
FROM 
    YourTableName t
CROSS APPLY 
    t.PeopleXML.nodes('/people/person') AS p(person_node);

代码说明： #

• YourTableName：替换成你的实际表名
• /people/person：XML节点的XPath路径，精准定位到每个person节点
• value('@Name', 'nvarchar(100)')：提取person节点的Name属性，第二个参数是返回值的数据类型
• 如果要保留person节点为0的行，把CROSS APPLY改成OUTER APPLY，这样RowID对应的行即使没有person也会显示，Name和Lastname为NULL

如果你的XML结构更复杂，或者需要更灵活的处理，可以用OPENXML。不过需要注意先创建XML文档句柄，用完后要释放资源。

DECLARE @xmlDoc INT;

-- 遍历表中的每一行XML
DECLARE cur CURSOR FOR SELECT PeopleXML FROM YourTableName;
DECLARE @xml XML;

OPEN cur;
FETCH NEXT FROM cur INTO @xml;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 创建XML文档句柄
    EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @xml;

    -- 提取数据
    SELECT 
        t.RowID,
        x.Name,
        x.Lastname
    FROM 
        YourTableName t
    JOIN 
        OPENXML(@xmlDoc, '/people/person', 1)
        WITH (
            Name nvarchar(100) '@Name',
            Lastname nvarchar(100) '@Lastname'
        ) x
    WHERE 
        t.PeopleXML = @xml;

    -- 释放文档句柄
    EXEC sp_xml_removedocument @xmlDoc;

    FETCH NEXT FROM cur INTO @xml;
END

CLOSE cur;
DEALLOCATE cur;

你可以先创建测试表插入数据验证效果：

CREATE TABLE TestXMLTable (
    RowID INT PRIMARY KEY,
    PeopleXML XML
);

INSERT INTO TestXMLTable VALUES
(1, '<people><person Name="John" Lastname="Doe"/><person Name="Jane" Lastname="Smith"/></people>'),
(2, '<people><person Name="Bob" Lastname="Brown"/><person Name="Alice" Lastname="White"/><person Name="Charlie" Lastname="Green"/></people>'),
(3, '<people/>'); -- 包含0个person节点的情况

把上面方法中的YourTableName换成TestXMLTable，就能看到预期结果啦！

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