CODEWITHSUNDEEP
phpsqlxml
Robert Margeson
Robert Margeson

Reputation:

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.

Upvotes: 5

Views: 7728

Answers (3)

Marty Staas
Marty Staas

Reputation: 411

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 it for me. I used the stored procedure mentioned there, it however has a small bug if you want to import into another table than 't1'. Just replace line

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

with

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

Execute the procedure via

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

Before calling this procedure make sure the file to import is accessible, for instance in the data folder of mysql (/var/lib/mysql/) and execute it with a user with FILE grant.

Upvotes: 0

M&#225;rio Rodrigues
M&#225;rio Rodrigues

Reputation: 862

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

This allow you to do something like this:

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

Upvotes: 1

gx.
gx.

Reputation: 403

No paying required, 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.

Upvotes: 0

Related Questions