Import XML in MySQL 5.1

Question

I apologize for the lack of knowledge ... I know that there is a lot of documentation on the Internet related to this, but I still do not understand.

My situation is this:

I have an XML file that I need to import and end up replacing it daily.

<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 several column names automatically and import them into several tables in my database.

For example,

  <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 to the table 'products'

Columns: products_description, products_name

import to table 'product_description

What about a product_id that is automatically generated? I can send the SQL output of the table structure.

I really appreciate the help ... I am willing to pay if they are ready to create a fully automated procedure for importing this file into my database; I use Zen Cart to place a basket.

Answer 1

You should read about this - load the XML into MySQL http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

This will allow you to do something like this:

 mysql> LOAD XML LOCAL INFILE 'items.xml' -> INTO TABLE item -> ROWS IDENTIFIED BY '<item>'; 

Answer 2

No cost to pay; Using XML in MySQL 5.1 and 6.0 will answer most of your questions. Also, go back to the top and read the entire page; you can do a lot with XML and MySQL.

Answer 3

Yes, thanks gx, http://web.archive.org/web/20100105150533/http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html#xml-5.1- importing did this for me. I used the stored procedure mentioned there, however it has a small error if you want to import it into a table other than "t1". Just replace the line

 SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')'); 

from

 SET @ins_text = CONCAT('INSERT INTO ', database_name, '.', table_name, ' (', ins_list, ') VALUES (', val_list, ')'); 

Follow the procedure with

 call xmldump_load('<filename>', '<schema>', '<tablename>'); 

Before calling this procedure, make sure that the file for import is available, for example, in the mysql data folder (/ var / lib / mysql /) and executes it with the user with FILE.