Quote:
Originally Posted by kushalkoolwal
Does any body know of a Linux based tool that can load a xml data file into a mysql database?
|
Two choices:
1. Create a table looking like:
Code:
create table xml_feed (
url character varying;
xmlcontent character varying;
);
And treat the data as just plain data.
You prepare a statement like:
Code:
insert into xml_feed (url, xmlcontent) values (?, ?);
Then execute it with the two values $URL and $XMLCONTENT.
(The point of the prepared statement is to avoid building up a query
where you'd have to escape special characters.)
2. Create some sort of tree hierarchy where each XML element/attribute is set up as a separate record.
In a DBMS that gracefully supports relational features like foreign keys, stored procedures, tree walking or other such approaches to hierarchical data, it's AT BEST, painful. And it's probably not worth doing even WITH spectacular support for that sort of thing.
But relational databases aren't terribly good at dealing with hierarchical data, and that's what XML is.
*BUT*, those options being thrown out there, XML is free-form, and can contain pretty much whatever you'd like. So there's no 'cookie-cutter' program that would fit what you'd like, either on the XML side, or on the MySQL side. Anything you see would need heavy modification to fit. If I had to do it (and I've HAD to do it in the past...
), I'd crank out a simple Perl program to read the XML data (lots of CPAN modules to help you with that), and shovel it into the MySQL database. Or, you can 'sanitize' the data, and get it into a delimited form (like comma-separated or tab separated), and use a quick import function like this:
Code:
LOAD DATA LOCAL INFILE 'infile.txt' INTO TABLE Street FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (Field1, Field2, Field3);
The "IGNORE 1 LINES" tells it there's a header field in the input file...you can leave that out if you have 'pure' data....
