Importing Data from an XML Document into a MySQL Table

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Importing Data from an XML Document into a MySQL Table

Postby jayrdi » 25. June 2014 09:40

Hi all, thanks for reading.

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:

Code: Select all
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
jayrdi
 
Posts: 2
Joined: 25. June 2014 09:20
Location: Newcastle Upon Tyne
Operating System: Windows 8

Re: Importing Data from an XML Document into a MySQL Table

Postby JJ_Tagy » 25. June 2014 11:24

Have you thought about loading the data into a temp table then run a query to extract the fields you want?
JJ_Tagy
 
Posts: 678
Joined: 30. January 2012 13:44
Operating System: Windows 10 Pro x64

Re: Importing Data from an XML Document into a MySQL Table

Postby jayrdi » 25. June 2014 13:07

That's a good idea, thanks, I'll give that a try...
jayrdi
 
Posts: 2
Joined: 25. June 2014 09:20
Location: Newcastle Upon Tyne
Operating System: Windows 8


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 35 guests