Get XML structure in SQL SERVER

Question

I have an XML variable, for ex.

DECLARE @xml XML = 
'<A>
 <AA>aa</AA>
 <AB>
   <ABA>aba</ABA>
 </AB>
</A>
<B>b</B>
<C>
 <CA>ca</CA>
</C>

I want to get a structure of this XML- table with one VARCHAR kolumn:

structure (VARCHAR)
--------------------
'A/AA'
'A/AB/ABA'
'B'
'C/CA'.

I don't need to get text in node- i need only structure. XML variable can be different (i don't know number of nodes, name of nodes, etc.).

Variable @xml can be without ROOT element.

I tried many combinations of .value() or .nodes(), but it didn't works. Best result give me an operation:

SELECT
    grandparent.gname.value('fn:local-name(.)', 'VARCHAR(MAX)'),
    parent.pname.value('fn:local-name(.)', 'VARCHAR(MAX)'),
    child.cname.value('fn:local-name(.)', 'VARCHAR(MAX)')
FROM
    @xml.nodes('*') AS grandparent(gname)
CROSS APPLY
    grandparent.gname.nodes('*') AS parent(pname)
CROSS APPLY
    parent.pname.nodes('*') AS child(cname)

It gaves me 'A/AB/ABA', but if i don't know number of nodes and nodes names, it is useless to me to continue.

Answer 1

Use a recursive CTE to extract the nodes one level at a time. The anchor part extract the root nodes and query('*') gets the child nodes for each node found. exist('*') is used to filter out the intermediate rows that is created during the recursion. The recursive part does the same as the anchor only it uses the XML provided in SubNodes instead.

declare @xml xml = 
'<A>
 <AA>aa</AA>
 <AB>
   <ABA>aba</ABA>
 </AB>
</A>
<B>b</B>
<C>
 <CA>ca</CA>
</C>';

with C as
(
  select T.X.value('local-name(.)', 'nvarchar(max)') as Structure,
         T.X.query('*') as SubNodes,
         T.X.exist('*') as HasSubNodes
  from @xml.nodes('*') as T(X)
  union all 
  select C.structure + N'/' + T.X.value('local-name(.)', 'nvarchar(max)'),
         T.X.query('*'),
         T.X.exist('*')
  from C
    cross apply C.SubNodes.nodes('*') as T(X)
)
select C.Structure
from C
where C.HasSubNodes = 0;

Result:

Structure
---------
B
C/CA
A/AA
A/AB/ABA