How can I load xml through mySql?

Question

On Windows (r) I can this statement to load xml data in the database

LOAD XML LOCAL INFILE 
'c:/users/yourname/desktop/myfile.xml'
 INTO TABLE MyTable
  ROWS IDENTIFED BY '<product>'

But, I cannot seem to accomplish this on Ubuntu 11.2.0 with the latest version of MySQl communiuty server installed.

This is the statement I use for my Ubuntu setup.

 LOAD XML LOCAL FILE '/home/ryan/tech.xml'
 INTO TABLE `t`
 ROWS IDENTIFIED BY '<product>';

So my question is, where do I put the XML files on my web server if I want to access them with MySQL?

Answer 1

You have a mistake on the first line

LOAD XML LOCAL FILE '/home/ryan/tech.xml'

It is not just FILE it is INFILE

LOAD XML LOCAL INFILE '/home/ryan/tech.xml'

Read more: http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

All commands or queries are same in windows and linux.

Answer 2

You may also have an issue with line breaks - if the xml file was created on Windows, its line breaks will be different to Ubuntu's - see http://en.wikipedia.org/wiki/Newline