SQL Server 2017中XML转关系表失败问题求助

你的问题出在对XML节点和属性的路径定位错误上，原查询试图直接从<file>节点获取子节点的属性和元素值，这显然行不通。我们需要修正XPath表达式，精准定位到对应的子节点和元素：

修正后的查询语句 #

DECLARE @XML XML = "<file id=""12aa""><vd>3</vd> <pl_a type_a =""111"" k=""111"" name=""aaa""></pl_a> <period from=""2019-04-01"" to=""2019-06-30""></period> <all>1</all> </file> <file id=""12bb""><vd>3</vd> <pl_b type_b =""222"" k=""222"" name=""bbb""></pl_b> <period from=""2019-04-01"" to=""2019-06-30""></period> <all>2</all> </file>"

SELECT 
    a.c.value(N'@id', N'nvarchar(150)') AS ID,
    -- 定位到pl_a子节点的type_a属性
    a.c.value(N'(pl_a/@type_a)[1]', N'nvarchar(150)') AS type_a,
    -- 定位到pl_b子节点的type_b属性
    a.c.value(N'(pl_b/@type_b)[1]', N'nvarchar(150)') AS type_b,
    -- 用COALESCE取pl_a或pl_b中的k属性，因为每个file只会有其中一个
    COALESCE(
        a.c.value(N'(pl_a/@k)[1]', N'nvarchar(150)'),
        a.c.value(N'(pl_b/@k)[1]', N'nvarchar(150)')
    ) AS k,
    -- 同理取name属性
    COALESCE(
        a.c.value(N'(pl_a/@name)[1]', N'nvarchar(150)'),
        a.c.value(N'(pl_b/@name)[1]', N'nvarchar(150)')
    ) AS name,
    -- 定位到period子节点的from属性
    a.c.value(N'(period/@from)[1]', N'nvarchar(150)') AS dt_from,
    -- 定位到period子节点的to属性
    a.c.value(N'(period/@to)[1]', N'nvarchar(150)') AS dt_to,
    -- <all>是元素而非属性，用text()获取其文本值
    a.c.value(N'(all/text())[1]', N'nvarchar(150)') AS all_
FROM @XML.nodes('file') AS a(c)

关键修改点说明 #

• 子节点属性定位：原查询直接用@type_a是从<file>节点取属性，但type_a实际属于<pl_a>子节点，所以需要用(pl_a/@type_a)[1]这样的路径，[1]是确保只取第一个匹配的节点（避免重复值）。
• 元素值获取：<all>是一个元素，不是属性，所以不能用@all，必须用(all/text())[1]来获取它的文本内容。
• 兼容pl_a/pl_b两种节点：因为每个<file>要么有<pl_a>要么有<pl_b>，所以用COALESCE函数来优先取其中一个的k和name属性，确保不会出现无效的NULL（你的示例里每个file都有其中一个节点）。

执行这个查询后，就能得到你预期的结果：

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