Reputation: 71
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
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