Say you need to load a table from an xml document, and this table defines some hierarchy. Believe me or not, but this is not that case when its better to store xml in the table.

Let's presume the table has:

• Node - document node id;
• Parent - parent node id;
• Name - node name.

The following defines a sample xml document we shall work with:

declare @content xml;

set @content = '
<document>
  <header/>
  <activity>
    <title/>
    <row/>
    <row/>
    <row/>
    <row/>
    <total/>
  </activity>
  <activity>
    <title/>
    <row/>
    <total/>
  </activity>
  <activity>
    <title/>
    <row/>
    <total/>
  </activity>
  <activity>
    <title/>
    <row/>
    <row/>
    <row/>
    <total/>
  </activity>
</document>';

How would you solved this task?

I've been spending a whole day building acceptable solution. This is probably because I'm not an SQL guru. I've found answers using cursors, openxml, pure xquery, and finally hybrid of xquery and sql ranking functions.

The last is fast, and has linear dependency of working time to xml size.

with NodeGroup(ParentGroup, Node, Name) as
(
  select 
    dense_rank() over(order by P.Node),
    row_number() over(order by N.Node),
    N.Node.value('local-name(.)', 'nvarchar(max)')
  from 
    @content.nodes('//*') N(Node) 
    cross apply
    Node.nodes('..') P(Node)
),
Node(Parent, Node, Name) as
(
  select 
    min(Node) over(partition by ParentGroup) - 1, Node, Name
  from 
    NodeGroup 
)
select * from Node order by Node;

Is there a better way? Anyone?