Shred XML and keep hierarchy information

• TommyF

  Old Hand

  Points: 332

  More actions

  April 27, 2010 at 4:25 am

  #73499

  Hi,

  I have a well formatted XML stored in XML Column.

  I need to shred the XML into a relational format.

  XML contains hierarchy of 3 levels.

  There is a need to keep hierarchy information in the new table.

  only "name" attribute is of interest to me.

  I'm using SQL Server 2005.

  Any help would be great.

  This is a snippet from my XML:

  <xml xml:lang="en">

  <Sect itemtoshow="6" name="Automobile" pg="3328">

  <Prod name="Brakes" pg="131">

  <Key name="Air Brakes" pg="33" />

  <Key name="Auto Brakes" pg="53" />

  </Prod>

  <Prod name="Car" pg="11168">

  <Key name="Aircon" pg="92" />

  </Prod>

  </Sect>

  <Sect itemtoshow="6" name="Building" pg="3323">

  <Prod name="Glass" pg="0">

  <Key name="This Glass" pg="2" />

  <Key name="Bullet Glass" pg="4" />

  </Prod>

  <Prod name="Roof" pg="66">

  <Key name="Brick" pg="39" />

  <Key name="Marble" pg="255" />

  </Prod>

  </Sect>

  </xml>

  I'm expected to have a table similar to this (or any other format that shows hierarchy):

  Tag ParentTag

  Automobile NULL

  Brakes Automobile

  Air Brakes Brakes

  Auto Brakes Brakes

  Car Automobile

  Aircon Car

  Building NULL

  Glass Building

  Thin Glass Glass

  Bullet Glass Glass

  Roof Building

  Brick Roof

  Marble Roof

  Thank you,

  Arik.

• TommyF

  Old Hand

  Points: 332

  More actions

  April 27, 2010 at 5:13 pm

  #1158592

  Problem solved. Thanks.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  April 28, 2010 at 3:51 am

  #1158741

  Would you mind posting your solution? It might help others, too.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  April 28, 2010 at 4:44 am

  #1158759

  Something like this?

  DECLARE @x XML

  SET @x='

  <xml xml:lang="en">

  <Sect itemtoshow="6" name="Automobile" pg="3328">

  .

  .

  </Sect>

  </xml>

  '

  SELECT r.value('.','VARCHAR(30)') AS Tag,

  r.value('../../@name','VARCHAR(30)') AS ParentTag

  FROM @x.nodes('//@name') AS x(r)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• TommyF

  Old Hand

  Points: 332

  More actions

  April 29, 2010 at 3:52 am

  #1159513

  Thanks Mark 🙂

  Such a nice solution.