将XML导入MySQL 5.1 [英] 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.

我的情况是这样:

我有一个XML文件，需要导入该文件，并最终每天将其替换.

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.

例如，

    <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>

然后导入到MySQL DB

And then import into MySQL DB

列: products_weight，products_model，products_image，products_price，products_last_modified

Columns: products_weight, products_model, products_image, products_price, products_last_modified

导入表产品"

列: 产品说明，产品名称

Columns: products_description, products_name

导入表'product_description

import into table 'product_description

又如何自动创建product_id?我可以发送表结构的SQL输出.

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

我真的很感谢您的帮助...如果他们愿意创建一个完全自动化的过程来将该文件导入到我的数据库中，我愿意付一些钱.我正在使用Zen Cart托管我的购物车.

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.

推荐答案

您应该阅读以下内容-将XML加载到MySQL中 http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

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>';

这篇关于将XML导入MySQL 5.1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！