Newbie here and trying to extract "shoedept or ShoeDept, $50, 30%off special,Top quality design,latitude,longitude" from file.xml to a phpmyadmin DB. myTable, which seems stubborn and can't be changed, has the same fields in the same order, except named differently as; type,price,headline,description,latitude,longitude.
<source>
<id>shoedept</id>
<name>ShoeDept</name>
</source>
<location>
<address>174 Sioux</address>
<zip>48228</zip>
<citycode>usa:mi:detroit</citycode>
<name>Detroit</name>
<state>MI</state>
<country>USA</country>
<latitude>42.4089</latitude>
<longitude>-82.9432</longitude>
</location>
<images/>
<time>1444207824</time>
<paid>No</paid>
<score>1</score>
<user/>
<url>
http://................
</url>
<attributes>
<features>Leather</features>
<men>1</men>
<women>2</women>
<currency>USD</currency>
<fee>No</fee>
<has_pic>No</has_pic>
<price>50</price>
<price_display>$50</price_display>
</attributes>
</element>
<element>
<id>4030773537</id>
<title>
$50 / special
</title>
<body>
Top quality design
</body>
Wen I write this statement;
LOAD DATA INFILE "file.xml" INTO TABLE myTable(headline,description.....)
I get tons of rows with all the unwanted junk listed. I only want one row from this whole block with the fields listed as columns. When I try to use
ROWS IDENTIFIED BY '<field>';
to condense this into one row, it throws an error. Then I tried to create a temp table and force the data into myTable with no hope below;
LOAD DATA INFILE "file.xml";
UPDATE tmp SET DEFAULT = 'file.xml';
INSERT INTO myTable SELECT * FROM tmp;
Thanks much
INFILE has to be a text file. Perhaps you can convert it with this tool: http://www.convertcsv.com/xml-to-csv.htm or something similar.
Then import the .csv file into the database.
As @Marc B mentioned, there is a LOAD XML command (https://dev.mysql.com/doc/refman/5.5/en/load-xml.html).