Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get XML structure in SQL SERVER

Tags:

sql-server

xml

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.

like image 237
user3786572 Avatar asked Sep 03 '25 03:09

user3786572


1 Answers

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
like image 61
Mikael Eriksson Avatar answered Sep 05 '25 16:09

Mikael Eriksson