What is the easiest way to import XML to MYSQL

J Sharman

What is the easiest way to import an XML Datafeed from a URL into a MYSQL
Database? Possibly using PHP

Regards Joe

PS Please answer to group and joe@joesharman.co.uk

Jul 16 '05 #1

Spidah

Have a look at some of the PHP code written for handling Amazon.com's XML
based web services. The simplest code converts the XML data into an array
which is then used to build the required display pages.

My own demo associate site at http://retail.laughland.biz uses this method.

Once the data is in the array it is a simple matter to transfer it to a
database.

H G Laughland

http://www.laughland.biz
"the web site for web sites"

"J Sharman" <joe@joesharman.co.uk> wrote in message
news:bfmqod$d3a$1@titan.btinternet.com...[color=blue]
> What is the easiest way to import an XML Datafeed from a URL into a MYSQL
> Database? Possibly using PHP
>
> Regards Joe
>
> PS Please answer to group and joe@joesharman.co.uk
>
>
>[/color]

Jul 16 '05 #2

pittendrigh

"J Sharman" <joe@joesharman.co.uk> wrote in message news:<bfmqod$d3a$1@titan.btinternet.com>...[color=blue]
> What is the easiest way to import an XML Datafeed from a URL into a MYSQL
> Database? Possibly using PHP
>
> Regards Joe
>
> PS Please answer to group and joe@joesharman.co.uk[/color]

I don't think there is an easy way. XML is like a freeform n-ary
tree, where each node has any number of children.

You could model that with some sort of node structure that
holds an array of pointers to all its children.

Alternately (and easier to implement with sql) you could model
nodes in an XML tree as relational tables where each
node (each table) holds a numerical
primary key plus a foreign key pointer to that node's parent.

Something like the following:

create table node
(
nid int primary key auto_increment, #pk for this node
rid int references node(nid),#points to the root node of tree
pid int references node(nid),#points to this node's parent
name VARCHAR(48) not null,
xpath VARCHAR(128), #the xpath to the node
value TEXT,
indexes(??)
)


For any database that really supports foreign keys,
you'd have trouble with the recusive node assigments
above: at least when you tried to initialize the root
node of a tree (real foreign keys have to point to
already existing keys).
So for Oracle or Postgres, you have to initialize the
table without the foreign key constraints, and
then add the fk attributes with an 'altertable'
command--after inserting the data.

Sql joins over a table like that are a nightmare, however.
The best solution is to forget about relational databases
and use a native XML database. Then you can query the tree
with XPath statements and/or XQuery, which were designed
with tree-like data in mind.

Jul 16 '05 #3

Christopher Browne

A long time ago, in a galaxy far, far away, "J Sharman" <joe@joesharman.co.uk> wrote:[color=blue]
> What is the easiest way to import an XML Datafeed from a URL into a MYSQL
> Database? Possibly using PHP[/color]

Two choices:

1. Create a table looking like:

create table xml_feed (
url character varying;
xmlcontent character varying;
);

And basically treat the data as just plain, well, data.

You prepare a statement like:
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.

This would be painful _at best_ in a DBMS that gracefully supports
relational features like foreign keys, stored procedures, tree walking
or other such approaches to hierarchicalizing things, and such, and
probably not worth doing even WITH spectacular support for that sort
of thing.

But relational databases aren't terribly good at dealing with
pathologically hierarchical data, and that's what XML is.

So all that can be commended is approach #1.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://cbbrowne.com/info/xml.html
[Message From The Dover at MIT-AI 10:55:60]
HELP ME! HELP ME! MY PAPER FEED IS JAMMED! DO YOU KNOW WHAT IT'S LIKE TO
HAVE YOUR PAPER FEED JAMMED?

Jul 16 '05 #4

Nikolai Chuvakhin

"J Sharman" <joe@joesharman.co.uk> wrote in message
news:<bfmqod$d3a$1@titan.btinternet.com>...[color=blue]
>
> What is the easiest way to import an XML Datafeed from a URL
> into a MYSQL Database? Possibly using PHP[/color]

Easiest in terms of what -- ease of coding, use of memory,
execution time, something else? What are the dimensions of the
task -- 3 kb daily or 4 Mb per minute? How far along are you in
deciding how the XML should be mapped onto the database structure?
Is this a one-time task or a repetitive routine?

Here are a few options, just to get you started:

A. The memory-saving, but slow, one

Get one XML entity equivalent to a database record at a time,
parse, and insert. Say, you have an XML entity:

<event id="387629">
<date>2003-07-12</date>
<desc>This event cannot be adequately described</desc>
</event>

which can be mapped to a database query:

INSERT INTO events SET
id = 387629,
date = '2003-07-12',
desc = 'This event cannot be adequately described';

Running one query at a time will be slow because of the fixed
per-query overhead.

B. A faster one

Get a bunch of record equivalents at a time, parse them,
store the data as CSV, and then use LOAD DATA INFILE.
Using the <event> entity from the example above, you could
have a CSV file like this:

387629,"2003-07-12","This event cannot be adequately described"
387632,"2003-07-14","Just a non-descript event"
[more records like this]
786523,"2003-07-23","This is the last event"

MySQL will gladly (and VERY quickly) swallow this.

Here, the bottleneck will be writing records into the file.

C. A very fast (and memory-hungry) one

Get all record equivalents you want, parse them, and combine
the results into one monstrous query like this:

INSERT INTO events (id, date, desc) VALUES (
(387629,'2003-07-12','This event cannot be adequately described'),
(387632,'2003-07-14','Just a non-descript event'),
[more records like this]
(786523,'2003-07-23','This is the last event')
);

Everything wraps up in one query, but you risk running out of
memory if there is a lot of data. There is a workaround,
however; run the query every time the query string exceeds
certain length, and then begin to compose a new one.
Essentially, you'd be doing block writes...

Cheers,
NC

Jul 16 '05 #5