使用元素将xml加载到mysql表中 [英] Load xml into mysql table with element

问题描述

我正在尝试在表中加载xml文件.问题是XML元素与它们的数据一起被插入到表中.

I am trying to load an xml file in a table. The issue is that XML elements are being inserted into the table together with their data.

这是表格字段:

程序名 程式网址 目录名称 最近更新时间 姓名 关键字 描述 ku 制造商 制造商编号 上层 货币 价格 Buyurl 印象网址 图片网址 广告客户类别 促销文字 库存

programname programurl catalogname lastupdated name keywords description sku manufacturer manufacturerid, upc currency price buyurl impressionurl imageurl advertisercategory promotionaltext instock

这是我的代码

LOAD DATA LOCAL INFILE '/home/public_html/apw.xml' INTO TABLE 
apw ROWS IDENTIFIED BY '<product>' FIELDS TERMINATED BY '\n';
(id,
programname,
programurl,
catalogname,
lastupdated,
name,
keywords,
description,
sku,
manufacturer,
manufacturerid,
upc,
currency,
price,
buyurl,
impressionurl,
imageurl,
advertisercategory,
promotionaltext,
instock);

因此，例如，在字段programname中插入了以下数据: 汽车零部件仓库"而不是汽车零部件仓库" 和programurl"url"而不是"url" 我做错了什么

So for instance, the field programname got inserted with data of "Auto Parts Warehouse" instead of "Auto Parts Warehouse" and programurl "url" instead of "url" What iam i doing wrong>

推荐答案

在FIELDS TERMINATED ...

您也应该使用LOAD XML ...语法.参见 http://dev.mysql.com/doc/refman/5.5/en/load-xml.html :

You should use the LOAD XML ... Syntax, too. See http://dev.mysql.com/doc/refman/5.5/en/load-xml.html:

The LOAD XML statement reads data from an XML file into a table

您的LOAD DATA INFILE ...用于将平面文件(csv)加载到表而不是XML:

Your LOAD DATA INFILE ... is used to load a flat-file (csv) to a table, not a XML:

The LOAD DATA INFILE statement reads rows from a text file into a table 

这篇关于使用元素将xml加载到mysql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！