Best way to import xml data into SQL Server Express 2005

I need to import some XML data into SQL Server Express 2005 and I'm wondering what would be the way to do it? Below is a sample of the xml that I need to import. I can probably write some code to do it if I have to. But I would prefer an easier way. I'm using SQL Server Express 2005, so that looks like it rules out using SSIS.

<?xml version="1.0" encoding="UTF-8" ?>
<tour>
  <tourName>Highlights of Jordan</tourName>
  <dossierCode>DWHJ</dossierCode>
  <tripDescription>Explore ancient sites and lost cities, overnight in a Bedouin tent and dine with the locals amidst the dramatic desert scenery of Wadi Rum. Take in Petra's fascinating history and marvel at its thrilling beauty—and still have a chance to relax in the therapeutic waters of the Dead Sea. This eight-day trip is ideal for the traveller who is short on time but wants to soak up the colours, culture and history of a country that's small in size but big in adventure.</tripDescription>
  <tripStyle>Classic</tripStyle>
  <year>2011</year>
  <brochureSupplier>Gap Adventures</brochureSupplier>
  <tripCountries>Jordan</tripCountries>
  <tripContinents>North Africa / Middle East</tripContinents>
  <startCountry>Jordan</startCountry>
  <finishCountry>Jordan</finishCountry>
  <startCity>Amman</startCity>
  <finishCity>Amman</finishCity>
  <tripActivities>Culture/History</tripActivities>
  <duration>8</duration>
  <physicalGrading>3</physicalGrading>
  <serviceLevel>Standard</serviceLevel>  
  <groupSize>
    <minimum>5</minimum>
    <maximum>15</maximum>
  </groupSize>
  <briefItinerary>
    <briefItineraryItem label="Day 1 Amman">Arrive at any time.</briefItineraryItem>
    <briefItineraryItem label="Day 2 Jerash, the Dead Sea (B)">Guided visit to the ancient Roman town of Jerash, trip to Dead Sea.</briefItineraryItem>
    <briefItineraryItem label="Days 3-4 Petra (2B)">Madaba mosaics, Mount Nebo, Kerak Castle, Petra entrance and guided tour.</briefItineraryItem>
    <briefItineraryItem label="Day 5 Wadi Rum (B,D)">Wadi Rum 4x4 jeep excursion, dinner and night in desert camp.</briefItineraryItem>
    <briefItineraryItem label="Day 6 Aqaba (B)">Optional snorkelling excursion.</briefItineraryItem>
    <briefItineraryItem label="Day 7 Amman (B)">Time to explore Amman.</briefItineraryItem>
    <briefItineraryItem label="Day 8 Amman (B)">Depart at any time.</briefItineraryItem>
  </briefItinerary>
  <detailedItinerary>
    <detailedItineraryItem label="Day 1 Arrive Amman">You are welcome to arrive in Amman at any time, as today is a designated arrival day with no planned activities. Please note that there will be an important welcome meeting this evening at our joining hotel. Please check the hotel notice board for more information on the location of this meeting or ask at reception. Please bring your passport and travel insurance documents to this meeting, as you will be required to fill out some important pre-trip admin documents. After learning more about our tour and plans for the following day, you are welcome to join our leader and your travelling companions for an optional dinner nearby. Overnight in Amman.</detailedItineraryItem>
    <detailedItineraryItem label="Day 2 Jerash, Dead Sea (B)">After breakfast at the hotel we will set off on our half day trip to Jerash, a fascinating place and one of the largest and most well preserved Roman sites outside Italy. Its paved and colonnaded streets, soaring hilltop temples, handsome theatres, spacious public squares and plazas, baths, fountains and city walls pierced by towers and gates remain in exceptional condition. The drive takes less than an hour each way, but will transport you some 2000 years back in time. After lunch, we head to the Dead Sea. The Dead Sea is actually a lake, without any from of life whatsoever existing in its waters. The water is so dense that it is practically impossible to sink, being five to ten times as salty as regular sea water. We base ourselves at a resort for the day, where you will have access to the beach. While swimming, cover yourself with the sea's mud, which is reputed to have special healing powers, and don't forget to take a photo of yourself reading a book while floating! You will notice that the salt penetrates any cuts on your body and has an unusual smell, but there are showers to wash off under after swimming. Afterwards, while away under an umbrella next to one of the fresh water swimming pools. There are a few options for your lunch today and the choice is yours, ranging from a full buffet at the resort or a snack from the cafe. We will return to Amman for the evening. Overnight in Amman. Approx travel times: Amman to Jerash开发者_运维知识库, 2hrs (return); Amman to Dead Sea, 4hrs (return)</detailedItineraryItem>
  </detailedItinerary>
</tour>

---

OPENROWSET supports the BULK keyword which helps us import xml files in sql server 2005. Here is an example that I found:

CREATE TABLE XmlImportTest
(
xmlFileName VARCHAR(300),
xml_data xml
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT  @xmlFileName = 'c:\TestXml.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData 
FROM
(
SELECT  * 
FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
SELECT * FROM XmlImportTest

DROP TABLE XmlImportTest

Reference

---

You can read here http://msdn.microsoft.com/en-us/library/ms345117%28v=sql.90%29.aspx about xml support in SQL Server 2005.

Some code showing what you can do.

declare @xml as xml

set @xml = 
'<?xml version="1.0" encoding="UTF-8" ?>
<tour>
  <tourName>Highlights of Jordan</tourName>
  <dossierCode>DWHJ</dossierCode>
  <tripStyle>Classic</tripStyle>
  <year>2011</year>
</tour>  
'

select 
  t.value('tourName[1]', 'varchar(50)'),
  t.value('dossierCode[1]', 'varchar(50)'),
  t.value('tripStyle[1]', 'varchar(50)'),
  t.value('year[1]', 'int')
from @xml.nodes('tour') t(t)

---

Thanks for you responses. In the end, I just wrote some code to parse the XML, and did a bulk insert