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.

I would seriously think about reviewing your table structure as a multicolumn definition as you're implying would be a nightmare to maintain. As the link in the Rubens Faria's answer points out, you'd be better off with an hierarchical table, i.e. a self-referencing table, than a multicolumn like this. — Paulo Santos
– Paulo Santos, Commented Jan 5, 2010 at 23:53
@Paulo - I appreciate your concern, so how would I translate the above XML into a self-referencing table with just two columns? - I think I need to take my XML and perform the inverse operation of what Ruben's answer shows. At that point it becomes trivial to self-join to the table and generate what I'm originally looking for. — Jeff Meatball Yang
– Jeff Meatball Yang, Commented Jan 7, 2010 at 5:52

Rubens Farias · Accepted Answer · 2010-01-05 23:48:33Z

1

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

answered Jan 5, 2010 at 23:48

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to transform XML structure to SQL columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related