Import XML into MySQL 5.1

Please excuse my lack of knowledge... I know there is a lot of documentation on the internet related to this but I still don't understand.

My situation is this:

I have an XML file that I need import and eventually replace daily with.

 <item>
  <model>AA311-Pink</model>
  <title>1122</title>
  <price>19.43</price>
  <category>cat</category>
  <loc>/AA311.html</loc>
  <image>/aa311.jpg</image>
  <description>Item Info</description>
  <weight>0.45</weight>
  <option_type>Color-Color</option_type>
  <option_value>Pink-Pink</option_value>
  <suggested_retail>51.50</suggested_retail>
  <special_handling/>
  <manufacturer>Tantus</manufacturer>
  <manufacturer_code>VB5074 and VB5067</manufacturer_code>
  <packaging>Retail Packaging</packaging>
  <in_stock>Yes</in_stock>
  <lastupdated>2008-11-05 16:35:56</lastupdated>

I need to change a handful of the column names automatically and import them into multiple tables in my database.

For instance,

 <item>
  <products_model>AA315</products_model>
  <products_name>name</products_name>
  <price>19.43</price>
  <category>cat</category>
  <loc>/AA315.html</loc>
  <products_image>aa315.jpg</products_image>
  <products_description>info</products_description>
  <products_weight>0.44</products_weight>
  <option_type/>
  <option_value/>
  <products_price>51.50</products_price>
  <special_handling/>
  <manufactures_name>Tantus</manufactures_name>
  <manufacturer_code>VA5104</manufacturer_code>
  <packaging>Retail Packaging</packaging>
  <products_status>Yes</products_status>
  <products_last_modified>2008-11-05 16:35:27</products_last_modified>

And then import into MySQL DB

Columns: products_weight, products_model, products_image, products_price, products_last_modified

import into table 'products'

Columns: products_description, products_name

import into table 'product_description

Also what about the product_id that is automatically created? I can send SQL output of table structure.

I really apprecaite the help... I am willing to pay some if they are willing to create a fully automated procedure to import this file into my database; I am using Zen Cart to host my shopping cart.

ansaurus

tags:

views:

answers:

Import XML into MySQL 5.1

related questions