How to import part of xml to mysql table?

Question

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.

Answer 1

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.