CODEWITHSUNDEEP
phpmysqlxmlcsv
user3626925
user3626925

Reputation: 71

Can't extract xml to sql

New to xml->mysql extract, and I have an xml file trying to map out to a mysql table. xml file fields are different from my phpmyadmin table fields so I need to map them (can't change the fields or recreate tables) like below; test.xml;

<persons>
<person>
<name>Al</name>
<hobby>Golf</hobby>
<description>been doing this for years</description>
<age>45</age>
<latitude>42.7303</latitude>
<longitude>85.7741<longitude>
</person>
</persons>

tbTest fields are; id, player, experience, age, latitude, longitude, etc........ When I run this

LOAD DATA INFILE "test.xml" INTO TABLE tbTest()

Instead of one row, I get 16 rows with bits and pieces in each row and one of them has the full description, which is really all I need. But it should only be one row, right?

Then when I run this instead

LOAD DATA INFILE "test.xml" INTO TABLE listing ROWS IDENTIFIED BY ,    '<description>';

I get the one row I want but no data whatsoever.

I've also tried

LOAD XML INFILE 'test.xml' INTO TABLE 
tbTest(description,Longitude,Longitude) 
ROWS IDENTIFIED BY '<persons>';

Then I tried to add fields to tbTest(col1, col2, col3) VALUES (id='col3', player='col2'....) and no luck with that at all.

Thanks in advance

Upvotes: 0

Views: 57

Answers (1)

ThW
ThW

Reputation: 19512

Each person element node represents a row. Its children element nodes represent the columns.

LOAD XML INFILE 'test.xml' 
INTO TABLE tbTest
ROWS IDENTIFIED BY 'person';

Upvotes: 1

Related Questions