Importing Data from an XML Document into a MySQL Table

[John awson]

Hi all, thanks for reading. Wasn't sure which topic this should go under so apologies if I've put it in the wrong place.

I am using phpMyAdmin to try and store some data from an XML document into a MySQL database.

Using the SQL tab I am entering the following code to do this:

LOAD XML LOCAL INFILE 'C:\\xampp\\mysql\\data\\poetic_archive\\edit.xml'
INTO TABLE author
ROWS IDENTIFIED BY '<unittitle>';

This does work and imports the data into a previously created table called author. The problem is that there are many fields with the <unittitle> tag and I only want to import data from specific ones.

The way that this XML file specifies the different types of <unittitle> data is by their nested location in the document. For example, nested within a <c03> tag, unittitle refers to an author's name. Nested within a <c04> tag however, unittitle refers to a book title.

It seems that you can only specify a single parameter in the ROWS IDENTIFIED BY '<tagname>'; command so how do I tell it exactly which unittitle fields I want it to extract?

Thank you for your help,

John