How to transform XML structure to SQL columns

Question

I'm working with SQL Server 2005.

Let's say I have a document like this:

<Item Type="Menu" Name="File">
    <Item Type="Selectable" Id="Open"/>
    <Item Type="Selectable" Id="Close"/>
    <Item Type="Menu" Name="Export"/>
         <Item Type="Selectable" Id="As JPEG"/>
         ....
    and so on, with N-level of menus

How can I translate this into a SQL table with columns:

Selectable, Level1Menu, Level2Menu, Level3Menu

where Open and Close would have File as level1Menu and NULL for all the Menu columns, but As JPEG would have File and Export as level1Menu and level2Menu and NULL otherwise.

Thanks.

Answer 1

Please, take a look here: SQL Server 2005: Recursive Hierarchies to XML - CTEs vs. UDF