My next SQL puzzle (thanks to fabulous XQuery support in SQL Server 2005) is how to reconstruct xml from the hierarchy table. This is reverse to the "Load xml into the table".

Suppose you have:

  select Parent, Node, Name from Data

where
  (Parent, Node) - defines xml hierarchy, and
  Name - xml element name.

How would you restore original xml?

November 8, 2006 To my anonymous reader:

declare @content nvarchar(max);

set @content = '';

with Tree(Node, Parent, Name) as
(
  /* Source tree */
  select Node, Parent, Name from Data
),
Leaf(Node) as
(
  select Node from Tree
  except
  select Parent from Tree
),
NodeHeir(Node, Ancestor) as
(
  select Node, Parent from Tree
  union all
  select
    H.Node, T.Parent
  from
    Tree T inner join NodeHeir H on H.Ancestor = T.Node
),
ParentDescendants(Node, Descendats) as
(
  select
    Ancestor, count(Ancestor)
  from
    NodeHeir
  where
    Ancestor > 0
  group by
    Ancestor
),
Line(Row, Node, Text) as
(
  select
    O.Row, T.Node, O.Text
  from
    ParentDescendants D
    inner join
    Tree T
    on D.Node = T.Node
    cross apply
    (
      select D.Node * 2 - 1 Row, '<' + T.Name + '>' Text
      union all
      select (D.Node + D.Descendats) * 2, '</' + T.Name + '>'
    ) O
  union all
  select
    D.Node * 2 - 1, T.Node, '<' + T.Name + '/>'
  from
    Leaf D inner join Tree T on D.Node = T.Node
)
select top(cast(0x7fffffff as int))
  @content = @content + Text
from
  Line
order by
  Row asc, Node desc
option(maxrecursion 128);

select cast(@content as xml);