Filter out elements that don't have child elements:
FROM @xml.nodes('//*[*]')
Set selection predicates in location path (SQLXML) - SQL Server | Microsoft Learn[
^]
Based on your output, you also want to filter out the root node:
FROM @xml.nodes('/*/*[*]')
You also only seem to want a distinct list, which means you'll need to switch from
.query(...)
to
.value(...)
:
SELECT DISTINCT
Nodes.Name.value('local-name(.)', 'nvarchar(max)') As NAMES
FROM
@xml.nodes('/*/*[*]') As Nodes(Name)
;
value() Method (xml Data Type) - SQL Server | Microsoft Learn[
^]