如何将部分xml导入mysql表？ [英] How to import part of xml to mysql table?

问题描述

我想将大型xml文件中的某些项目导入mysql表。可以说xml包含数千个项目，例如：

 < CD> 
< TITLE> Empire Burlesque< / TITLE> 
< ARTIST> Bob Dylan< / ARTIST> 
< COUNTRY>美国< / COUNTRY> 
< COMPANY>哥伦比亚< / COMPANY> 
< PRICE> 10.90< / PRICE> 
< YEAR> 1985< / YEAR> 
< / CD> 
  

但是我只需要将每张CD的 TITLE和 YEAR导入到mysql的cd表中。

我知道有一个 LOAD XML mysql 5. +，用于从xml到mysql获取数据，但显然它映射了整个xml，这不是我所需要的。需要。因此，感谢您的帮助。

解决方案

感谢基础知识，我才发现从xml导入必需字段的最简单方法是mysql表将使用mysql的内置XML加载功能，例如：

  LOAD XML LOCAL INFILE'/ path / to / file.xml'INTO TABLE cds ROWS ID''< CD>';; 
  

您先前通过file.xml中需要的列创建cds表的位置。

请注意，在mysql 5.5+中，当您输入mysql命令行时需要添加'--local-infile'标志，否则会出现错误1148。 / p>

I'd like to import some of the items in an large xml file to mysql table. Lets say the xml contains thousands of items like:

<CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>

But I need only 'TITLE' and 'YEAR' of each CD to be imported to mysql's cd table.

I know that there is a 'LOAD XML' mysql 5.+ to get data from xml to mysql, but apparently it maps the whole xml, which is not what I need. So I appreciate your help.

解决方案

Thanks abase omment, I just figured out that the easiest way to import necessary fields from xml to mysql table is to use the built in Load XML feature of mysql, like:

 LOAD XML LOCAL INFILE '/path/to/file.xml' INTO TABLE  cds ROWS IDENTIFIED BY '<CD>';

Where you previously created cds table by the columns that you need from file.xml.

Just notice that in mysql 5.5+ you need to add '--local-infile ' flag when you enter the mysql command line, otherwise you get an ERROR 1148.

这篇关于如何将部分xml导入mysql表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！