How to transform XML structure to SQL columns

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.

---

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