emmetmurray
06-16-2008, 09:04 AM
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
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