extracting individual nodes of XML

  • 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

  • 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
  • Wow thanks Mark, I had dabbled with the nodes method, the path I chose was wrong.

    Works a treat many thanks again.

    Paul

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply