By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @x XML=N'<Level1>
<Level2 name="l2_name">
<Level3 name="l3_name">
<Level4 name="l4_name">
<Level5 name="l5_name">
<Level6 name="l6_name" value="l6_value"/>
<Level6 name="l6_name" value="l6_value"/>
</Level5></Level4></Level3></Level2></Level1>';
SELECT
x.n.value('../../../../@name', 'varchar(20)') as [col1],
x.n.value('../../../@name', 'varchar(20)') as [col2],
x.n.value('../../@name', 'varchar(20)') as [col3],
x.n.value('../@name', 'varchar(20)') as [col4],
x.n.value('./@name', 'varchar(20)') as [col5]
/*,
c2.value('@name', 'varchar(20)') as [col2],
c3.value('@name', 'varchar(20)') as [col3],
c4.value('@name', 'varchar(20)') as [col4],
c5.value('@name', 'varchar(20)') as [col5],
c5.value('@value', 'varchar(20)') as [col6]*/
FROM
@x.nodes('/Level1/Level2/Level3/Level4/Level5/Level6') AS x(n)
| col1 | col2 | col3 | col4 | col5 |
|---|---|---|---|---|
| l2_name | l3_name | l4_name | l5_name | l6_name |
| l2_name | l3_name | l4_name | l5_name | l6_name |