XML data import

• fleur.dunbar

  Grasshopper

  Points: 12

  More actions

  April 17, 2012 at 11:18 am

  #272867

  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 created the necessary tables and imported 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).

  Now I need to get the Ticket info into the table but the tag does not include a reference number, which I need to use as a foreign key.

  I tried the below and a few varieties of this but cannot get it right.

  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)

  Please help?

• Mike01

  SSChampion

  Points: 11420

  More actions

  April 17, 2012 at 2:16 pm

  #1475032

  I'm not sure what the results are that you are expecting. When I ran your query, it is showing the Value and taxes fields appropriately. I modified the XML to show a second ticket detail. Is this what you are looking for

  <?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>

  <FlightTickets>

  <TicketDetail>

  <Number>100</Number>

  <Value>222.00</Value>

  <Taxes>33.00</Taxes>

  </TicketDetail>

  </FlightTickets>

  </BookingElement>

  </BookingElements>

  </Booking>

  </BookingList> '

  select

  XmlQuery.Tickets.query('Number').value('.','varchar(50)'),

  XmlQuery.Tickets.query('../../../BookingElement/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)

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• fleur.dunbar

  Grasshopper

  Points: 12

  More actions

  April 18, 2012 at 3:04 am

  #1475203

  Yes that pulls through the Element reference but there is one slight problem:

  Apart from the reference it should have it pulls through the others

  so if there are 2 elements I get the reference of both and not only the one which the ticket refers to.

  Is there a way I can get that info only please?