extracting individual nodes of XML

• pnr8uk

  SSCommitted

  Points: 1828

  More actions

  September 26, 2012 at 4:29 am

  #262203

  I have a issue with XML stored in a SQL table where I want to extract the information from each node into its own column in another table. All works well until I have multiple values under the same node (see XML below)

  1. Easy to extract XML: NB: Booking is the root

  <Booking Reference= "1111111" Created ="2012-09-24">

  </Booking>

  To extract the reference and the created I have used:

  select

  xml_data.query('data(//Booking/@Reference)') as BReference,

  xml_data.query('data(//Booking/@Created)') as DateCreated from XMLData

  This works just fine, giving me:

  BReference DateCreated

  11111111 25-09-2012

  However my problem comes when I have TWO or more values in the same node:

  I am trying to extract Hotel info individually so I get two hotel names into two rows not as

  I currently have:

  2. <Booking Reference= "1111111" Created ="2012-09-24">

  -<Hotel Name="Villa Bella">

  </Hotel>

  -<Hotel Name="Sea View">

  </Hotel>

  </Booking>

  When I use the same query principal by ADDING.

  xml_data.query('data(//Booking/Hotel/@Name)') as HotelName

  The result I get is:

  BReference DateCreated HotelName

  11111111 25-09-2012 Villa Bella Sea View

  Whereas I want to pull out each line individually as:

  BReference DateCreated HotelName

  11111111 25-09-2012 Villa Bella

  11111111 25-09-2012 Sea View

  Any help greatfully received

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 26, 2012 at 4:47 am

  #1542303

  Use the 'nodes' function

  DECLARE @t TABLE(xml_data XML)

  INSERT INTO @t(xml_data) VALUES('

  <Booking Reference= "1111111" Created ="2012-09-24">

  <Hotel Name="Villa Bella">

  </Hotel>

  <Hotel Name="Sea View">

  </Hotel>

  </Booking>')

  SELECT n1.value('@Reference','VARCHAR(10)') AS BReference,

  n1.value('@Created','DATE') AS DateCreated,

  n2.value('@Name','VARCHAR(20)') AS HotelName

  FROM @t

  CROSS APPLY xml_data.nodes('Booking') AS booking(n1)

  CROSS APPLY booking.n1.nodes('Hotel') AS hotel(n2)

  ____________________________________________________

  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

• pnr8uk

  SSCommitted

  Points: 1828

  More actions

  September 26, 2012 at 4:50 am

  #1542304

  Wow thanks Mark, I had dabbled with the nodes method, the path I chose was wrong.

  Works a treat many thanks again.

  Paul