CODEWITHSUNDEEP
sql-server-2005parsingxml-parsing
Jeff Meatball Yang
Jeff Meatball Yang

Reputation: 39017

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.

Upvotes: 0

Views: 163

Answers (1)

Rubens Farias
Rubens Farias

Reputation: 57936

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

Upvotes: 1

Related Questions