XML flatten multiple hierarchical nodes SQL Server 2016

Question

I have a hierarchical XML structure that I need to flatten down to a simple list of XML elements.

This is the code I've tried so far:

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
    r.value('fn:local-name(.)', 'nvarchar(50)') AS Attribute,
    r.value('.','nvarchar(max)') AS Value    
FROM 
    @XmlString.nodes('//Orders/Order/*')  AS records(r)

Result:

Attribute | Value
-------------------
id        | 1
ref       | 123
from      | test addtest code
to        | test addtest code

Expected result:

Attribute | Value
id        | 1
ref       | 123
from_add  | test add
from_code | test code
to_add    | test add
to_code   | test code

Answer 1

Please try the following solution.

SQL

DECLARE @xml XML = 
N'<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 r.value('local-name(.)', 'nvarchar(50)') AS Element
    , r.value('text()[1]','nvarchar(max)') AS Value    
FROM @xml.nodes('/Orders/Order/*[local-name()=("id","ref")]')  AS t(r)
UNION ALL
SELECT r.value('local-name(..)', 'nvarchar(50)') + '_' +
    r.value('local-name(.)', 'nvarchar(50)') AS Element
    , r.value('text()[1]','nvarchar(max)') AS Value    
FROM @xml.nodes('/Orders/Order/*[local-name()=("from","to")]/*')  AS t(r);

Output

+-----------+-----------+
|  Element  |   Value   |
+-----------+-----------+
| id        | 1         |
| ref       | 123       |
| from_add  | test add  |
| from_code | test code |
| to_add    | test add  |
| to_code   | test code |
+-----------+-----------+