For some time ago, I posted an algorithm how to get the XML structure automatically. Today I stumbled across another approach which seems to be faster. Reservations though I haven't tested this against large xml data yet.

However, the previous algorithm relied on a WHILE loop here
http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx

but this new algorithm doesn't. It's all xml internal thingies going on.

DECLARE     @Nodes TABLE
            (
                         NodeID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                         ParentNodeName NVARCHAR(64),
                         NodeName NVARCHAR(64)
            )

DECLARE @Data XML

SET @Data = '
<root>
        <elementGroup>
                        <element>
                                     <stuff>
                                                 <comment>Stuff comment</comment>
                                     </stuff>
                                     <comment>Element comment</comment>
                        </element>
                        <comment>Element group comment</comment>
            </elementGroup>
            <comment>Root comment</comment>
</root>'

INSERT      @Nodes
            (
                         ParentNodeName,
                         NodeName
            )
SELECT      e.value('local-name(..)[1]', 'VARCHAR(MAX)') AS ParentNodeName,
            e.value('local-name(.)[1]', 'VARCHAR(MAX)') AS NodeName
FROM        @data.nodes('//*[local-name(.) > ""]') AS n(e)

;WITH Yak(NodeLevel, RootName, ElementName, NodeID, NodePath)
AS (
            SELECT     0,
                       ParentNodeName,
                       NodeName,
                       NodeID,
                       CAST(NodeID AS VARCHAR(MAX))
            FROM       @Nodes
            WHERE      ParentNodeName = ''

            UNION ALL

            SELECT     y.NodeLevel + 1,
                       n.ParentNodeName,
                       n.NodeName,
                       n.NodeID,
                       y.NodePath + ';' + CAST(n.NodeID AS VARCHAR(MAX))
            FROM       @Nodes AS n
            INNER JOIN Yak AS y ON y.ElementName = n.ParentNodeName
)

SELECT   RootName,
         REPLICATE('    ', NodeLevel) + ElementName AS ElementName,
         ROW_NUMBER() OVER (PARTITION BY RootName ORDER BY ElementName) AS SortedByElementName,
         ROW_NUMBER() OVER (PARTITION BY RootName ORDER BY NodeID) AS SortedByPresence
FROM     Yak
ORDER BY NodePath