sql-serverxml-import

Return all xml nodes import into SQL Server


I have imported an xml document into SQL Server, I am now trying to import various parts into different tables. When I use the below query it only returns one row of hotel_facilities, I need to return all the hotel_facilities with the hotel_ref.

DECLARE @Details xml 
    SET @Details = '<hotels>
 <hotel>
  <hotel_ref>105</hotel_ref> 
 <hotel_facilities>
  <id>2</id> 
  <name>Disabled Facilities</name> 
  <id>4</id> 
  <name>24 Hour Reception</name> 
  <id>12</id> 
  <name>Restaurant</name> 
  </hotel_facilities>
  </hotel>
</hotels>'  

SELECT tab.col.value('../hotel_ref[1]','varchar(100)') AS 'hotel_ref',
tab.col.value('./id[1]','varchar(100)') AS 'HotelFacilityID',
tab.col.value('./name[1]','varchar(100)') AS 'HotelFacilityName'
FROM @Details.nodes('//hotels/hotel/hotel_facilities') AS tab(col)

Solution

  • I agree with marc_s The XML does not have a good schema.

    The closest I could get is:

    SELECT tab.col.value('./hotel_ref[1]','varchar(100)') AS 'hotel_ref', 
    fac.value('(.)[1]','varchar(100)') AS 'HotelFacilityID', 
    ROWID=IDENTITY(int,1,1) 
    into #facilitiesid 
    FROM @Details.nodes('/hotels/hotel') AS tab(col) 
    cross apply col.nodes('.//id') a(fac) 
    
    
    SELECT tab.col.value('../hotel_ref[1]','varchar(100)') AS 'hotel_ref', 
    fac.value('(.)[1]','varchar(100)') AS 'HotelFacilityName', 
    ROWID=IDENTITY(int,1,1) 
    into #facilitiesnames 
    FROM @Details.nodes('//hotels/hotel/hotel_facilities') AS tab(col) 
    cross apply col.nodes('.//name') a(fac) 
    
    select i.hotel_ref, HotelFacilityID, HotelFacilityName 
    from #facilitiesid i 
    inner join #facilitiesnames n 
        on i.rowid = n.rowid