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)