Import XML data into SQL 2005

See more:

SQL-Server-2005

XML

SQL-Server

Hi,

I have the following xml data that I need to import into SQL.

<?xml version="1.0"?>
<BookingList> 
  <Booking> 
    <BookingRef>ABC-00110</BookingRef> 
    <BookingDate>04-11-12</BookingDate> 
      <BookingElements> 
        <BookingElement> 
          <BookingItem>1</BookingItem> 
          <ElementRef>ABC-00110/1</ElementRef> 
          <ElementType>Flight</ElementType> 
            <FlightTickets> 
              <TicketDetail> 
                <Number/> 
                <Value>1500.00</Value> 
                <Taxes>0.00</Taxes> 
              </TicketDetail> 
            </FlightTickets> 
        </BookingElement>
        <BookingElement> 
          <BookingItem>2</BookingItem> 
          <ElementRef>ABC-00110/2</ElementRef> 
          <ElementType>Hotel</ElementType> 
        </BookingElement> 
     </BookingElements> 
  </Booking> 
  <Booking> 
    <BookingRef>ABC-00111</BookingRef> 
    <BookingDate>04-04-12</BookingDate> 
      <BookingElements> 
        <BookingElement> 
          <BookingItem>1</BookingItem> 
          <ElementRef>ABC-00111/1</ElementRef> 
          <ElementType>Hotel</ElementType> 
        </BookingElement> 
      </BookingElements> 
    </Booking> 
</BookingList>

So far I managed to import the booking and the booking elements using the code below:

insert into Booking([BookingRef],[BookingDate])

select 
XmlQuery.Booking.query('BookingRef').value('.','varchar(50)'),
XmlQuery.Booking.query('BookingDate').value('.','datetime')
From
(
select cast (XmlQuery as XML)from openrowset(
Bulk 'C:\datafiles\data.xml',single_blob) as T(XmlQuery)
)as T(XmlQuery)
cross apply XmlQuery.nodes('BookingList/Booking') as XmlQuery(Booking)

insert into BookingElement([BookingItem],[ElementRef],[ElementType])

select 
XmlQuery.BookingElement.query('BookingItem').value('.','int'),
XmlQuery.BookingElement.query('ElementRef').value('.','varchar(50)'),
XmlQuery.BookingElement.query('ElementType').value('.','varchar(50)')
From
(
select cast (XmlQuery as XML)from openrowset(
Bulk 'C:\datafiles\data.xml',single_blob) as T(XmlQuery)
)as T(XmlQuery)
cross apply XmlQuery.nodes('BookingList/Booking/BookingElements/BookingElement') as XmlQuery(BookingElement)

Now what I need to do is to get the flight tickets information into a table. I need to get the element reference and the flight ticket info. I have tried the following but the reference does not pull through. (I didn't expect it to either).

insert into FlightTicket(TicketNumber,ElementRef,Value,Taxes)

select 
XmlQuery.Tickets.query('Number').value('.','varchar(50)'),
XmlQuery.Tickets.query('ElementRef').value('.','varchar(50)'),
XmlQuery.Tickets.query('Value').value('.','money'),
XmlQuery.Tickets.query('Taxes').value('.','money')
From 
(
select cast (XmlQuery as XML)from openrowset(
Bulk 'C:\datafiles\data.xml',single_blob) as T(XmlQuery)
)as T(XmlQuery)
cross apply XmlQuery.nodes('BookingList/Booking/BookingElements/BookingElement/FlightTickets/TicketDetail') as XmlQuery(Tickets)

How do I need to do this please?