SQL Server 2016多层级XML节点扁平化问题求助

可以通过调整XPath定位到所有叶子节点，并拼接父节点名称生成带层级标识的属性名，修改后的代码如下：

DECLARE @XmlString XML = CONVERT(XML, '<Orders><Order><id>1</id><ref>123</ref><from><add>test add</add><code>test code</code></from><to><add>test add</add><code>test code</code></to></Order></Orders>')

SELECT
    CASE 
        WHEN r.value('../fn:local-name(.)', 'nvarchar(50)') = 'Order'
            THEN r.value('fn:local-name(.)', 'nvarchar(50)')
            ELSE CONCAT(r.value('../fn:local-name(.)', 'nvarchar(50)'), '_', r.value('fn:local-name(.)', 'nvarchar(50)'))
    END AS Attribute,
    r.value('.','nvarchar(max)') AS Value    
FROM 
    @XmlString.nodes('//Orders/Order//*[not(*)]')  AS records(r)

代码说明 #

• //Orders/Order//*[not(*)]：XPath表达式定位到Order节点下所有没有子元素的叶子节点，直接获取id、ref、add、code这些最终文本节点，避免了原代码中把父节点的所有子文本拼接的问题。
• CASE语句：判断当前叶子节点的父节点是否为Order，若是则直接用当前节点名称作为属性名；否则将父节点名称与当前节点名称用下划线拼接，生成from_add、to_code这类带层级前缀的属性名。

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