LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 06-30-2009, 07:41 PM   #1
kushalkoolwal
Senior Member
 
Registered: Feb 2004
Location: Middle of nowhere
Distribution: Debian Squeeze
Posts: 1,249

Rep: Reputation: 49
xml and mysql


Does any body know of a Linux based tool that can load a xml data file into a mysql database?
 
Old 06-30-2009, 11:29 PM   #2
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 22,328

Rep: Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026
Quote:
Originally Posted by kushalkoolwal View Post
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....
 
Old 07-01-2009, 12:47 PM   #3
kushalkoolwal
Senior Member
 
Registered: Feb 2004
Location: Middle of nowhere
Distribution: Debian Squeeze
Posts: 1,249

Original Poster
Rep: Reputation: 49
Quote:
Originally Posted by TB0ne View Post
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.)
Thank you TBOne. I liked the first method. However what I don't get is why the need for two values - url and xmlcontent. Say I have my xml file at http://localhost/data.xml

How do I split into url and xmlcontent?
 
Old 07-02-2009, 09:21 AM   #4
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 22,328

Rep: Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026Reputation: 6026
Quote:
Originally Posted by kushalkoolwal View Post
Thank you TBOne. I liked the first method. However what I don't get is why the need for two values - url and xmlcontent. Say I have my xml file at http://localhost/data.xml

How do I split into url and xmlcontent?
Let's see...you said your XML file is at http://localhost/data.xml? So the URL would then be "http://localhost" and the XML content would be "data.xml".

And please re-read what I posted...this probably isn't going to do what you are wanting to do. A custom import routine is really the only way to get good, clean data, into a database, in a form you can actually USE.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL to XML - charset problems Boby Programming 1 04-20-2005 09:22 AM
XML <->MySql in Perl?? rsz Programming 0 10-06-2004 10:40 AM
html, xml, php, mysql atheist Programming 8 06-07-2004 02:28 PM
MySQL: Importing a database from an XML file concoran General 0 09-08-2003 03:58 AM
C++ question: MySQL or XML? BlackT Programming 7 07-21-2003 08:46 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 05:38 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration