Import xml live feed into sql

[emmetmurray]

Hi,

i need to import a large xml feed from a url into a sql server database.

My sql of knowledge is limited to sql statements in asp and searching databases , and too be honest i don't know where to start wih this.

I have researched it and understand there is a bulk load component in sql or SQLXML 4.0 or something. but don't know how to install, or get these running, and how to connect my xml feed to these.

Could anyone lay out the steps i need to take in order to get my feed into a database, and preferably time it to update evry 24hrs or so?

This is an example of the feed if that helps. It is nested and not flat, which could cause problems also.

- <event>
<event_token>MXJ</event_token>
<src_code>tptd</src_code>
<area_code>PAR</area_code>
<event_code>8441</event_code>
<venue_code>DIS/T</venue_code>
<src_desc>Theme Park Tickets Direct</src_desc>
<area_desc>Paris</area_desc>
<event_desc>1 Day / 2 Park Hopper</event_desc>
<venue_desc>Disneyland Resort® Paris</venue_desc>
<start_date_desc>Tue, 17th June 2008</start_date_desc>
<start_time_desc>09:00</start_time_desc>
<start_date_time_desc>Tue, 17th June 2008, 09:00</start_date_time_desc>
<start_date_yyyymmdd>20080617</start_date_yyyymmdd>
<start_time_hhmm>0900</start_time_hhmm>
<start_time_hhmmss>090000</start_time_hhmmss>
<start_utc_seconds>1213686000</start_utc_seconds>
<start_utc_offset>7200</start_utc_offset>
<end_date_desc>Fri, 3rd April 2009</end_date_desc>
<end_time_desc>09:00</end_time_desc>
<end_date_time_desc>Fri, 3rd April 2009, 09:00</end_date_time_desc>
<end_date_yyyymmdd>20090403</end_date_yyyymmdd>
<end_time_hhmm>0900</end_time_hhmm>
<end_time_hhmmss>090000</end_time_hhmmss>
<end_utc_seconds>1238742000</end_utc_seconds>
<end_utc_offset>7200</end_utc_offset>
<country_code>fr</country_code>
<postcode>77700</postcode>
- <geographic_info>
<event_lattitude>48.87260000</event_lattitude>
<event_longitude>2.77170000</event_longitude>
</geographic_info>
- <event_class>
<main_class>attract</main_class>
<sub_class>theme</sub_class>
</event_class>
<part_query_body>s_area=PAR&s_eve=8441&s_src=tptd&s_ven=DIS%2FT</part_query_body>
- <full_query>
<crypto_block>c_--bTWUoDG0GIgLYJ8muoe-UQ0YnVHRdRCDNJi8OhJ8svVB5QYbUuQe8vGHBShRH6yOQwrHzpN_APvUtz_L61fgJMc3OFTjiloCC4cHb4CZmbs_GdEP-xN6zfSsCffUMvAu7GYnsP9Nnm2-Y</crypto_block>
</full_query>
<need_departure_date>yes</need_departure_date>
<using_perf_list>yes</using_perf_list>
<using_usage_date>no</using_usage_date>
<currency_code>gbp</currency_code>
<min_seat_price>36.00</min_seat_price>
<max_seat_price>36.00</max_seat_price>
<min_fee_price>0.00</min_fee_price>
<max_fee_price>0.00</max_fee_price>
<lingo_code>attraction_tptd</lingo_code>
</event>


- <event>
<event_token>PYE</event_token>
<src_code>tptd</src_code>
<area_code>DBN</area_code>
<event_code>7417</event_code>
<venue_code>A5Z/T</venue_code>
<src_desc>Theme Park Tickets Direct</src_desc>
<area_desc>Dublin</area_desc>
<event_desc>1 Day Pass</event_desc>
<venue_desc>Dublin Tourism</venue_desc>
<start_date_desc>Tue, 17th June 2008</start_date_desc>
<start_time_desc>07:00</start_time_desc>
<start_date_time_desc>Tue, 17th June 2008, 07:00</start_date_time_desc>
<start_date_yyyymmdd>20080617</start_date_yyyymmdd>
<start_time_hhmm>0700</start_time_hhmm>
<start_time_hhmmss>070000</start_time_hhmmss>
<start_utc_seconds>1213682400</start_utc_seconds>
<start_utc_offset>3600</start_utc_offset>
<end_date_desc>Wed, 31st December 2008</end_date_desc>
<end_time_desc>07:00</end_time_desc>
<end_date_time_desc>Wed, 31st December 2008, 07:00</end_date_time_desc>
<end_date_yyyymmdd>20081231</end_date_yyyymmdd>
<end_time_hhmm>0700</end_time_hhmm>
<end_time_hhmmss>070000</end_time_hhmmss>
<end_utc_seconds>1230706800</end_utc_seconds>
<end_utc_offset>0</end_utc_offset>
<country_code>ie</country_code>
<postcode>Dublin 2</postcode>
- <geographic_info>
<event_lattitude>53.34420013</event_lattitude>
<event_longitude>-6.26840019</event_longitude>
</geographic_info>
- <event_class>
<main_class>sights</main_class>
<sub_class>tour</sub_class>
</event_class>
<part_query_body>s_area=DBN&s_eve=7417&s_src=tptd&s_ven=A5Z%2FT</part_query_body>
- <full_query>
<crypto_block>c_--bTWUoDG0GIgLYJ8muoe-UYIBvv26LPbmTwPh_CzymBMicRaMNtCHduGHBShRH6yOcmxrTe6nU8rUtz_L61fgJMc3OFTjiloCC4cHb4CZmbs_GdEP-xN6zfSsCffUMvAu7GYnsP9Nnm2-Y</crypto_block>
</full_query>
<need_departure_date>no</need_departure_date>
<using_perf_list>yes</using_perf_list>
<using_usage_date>no</using_usage_date>
<currency_code>gbp</currency_code>
<min_seat_price>25.00</min_seat_price>
<max_seat_price>25.00</max_seat_price>
<min_fee_price>0.00</min_fee_price>
<max_fee_price>0.00</max_fee_price>
<lingo_code>tour</lingo_code>
</event>


Thanks

[chazzy]

this MS knowledge base should get you going
http://support.microsoft.com/kb/316005

[emmetmurray]

Hi,

i used the link you sent, and got a basic test running fine. But this does not allow me to get the information from a xml webpage. I have searched everywhere and can find nothing on this subject.

I need to get the xml info from a web page either directly into a sql database, or onto my local so i can import it using sqlxmlbulkload. Any advice?

Thanks

[chazzy]

so you want something to read a page over HTTP? what language?