What is the easiest way to import XML to MYSQL

Discussion in 'XML' started by J Sharman, Jul 23, 2003.

  1. J Sharman

    J Sharman Guest

    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
    J Sharman, Jul 23, 2003
    #1
    1. Advertising

  2. J Sharman

    Spidah Guest

    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" <> wrote in message
    news:bfmqod$d3a$...
    > 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
    >
    >
    >
    Spidah, Jul 24, 2003
    #2
    1. Advertising

  3. J Sharman

    pittendrigh Guest

    "J Sharman" <> wrote in message news:<bfmqod$d3a$>...
    > 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


    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.
    pittendrigh, Jul 24, 2003
    #3
  4. A long time ago, in a galaxy far, far away, "J Sharman" <> wrote:
    > What is the easiest way to import an XML Datafeed from a URL into a MYSQL
    > Database? Possibly using PHP


    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?
    Christopher Browne, Jul 24, 2003
    #4
  5. "J Sharman" <> wrote in message
    news:<bfmqod$d3a$>...
    >
    > What is the easiest way to import an XML Datafeed from a URL
    > into a MYSQL Database? Possibly using PHP


    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
    Nikolai Chuvakhin, Jul 24, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. tom
    Replies:
    4
    Views:
    14,078
    jpowers5882
    Oct 24, 2008
  2. =?Utf-8?B?S2VubmV0aA==?=

    Easiest way to bind a grid datasource to a datatable

    =?Utf-8?B?S2VubmV0aA==?=, Jan 18, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    6,702
    =?Utf-8?B?S2VubmV0aA==?=
    Jan 18, 2004
  3. =?Utf-8?B?QmlsbCBCb3Jn?=

    Easiest way to get page name from error routine?

    =?Utf-8?B?QmlsbCBCb3Jn?=, Mar 2, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    434
    Tommy
    Mar 3, 2004
  4. Raymond Schanks
    Replies:
    0
    Views:
    326
    Raymond Schanks
    Jul 15, 2010
  5. Ryan Ternier

    Easiest way giving an XML file to a control

    Ryan Ternier, Nov 15, 2004, in forum: ASP .Net Web Controls
    Replies:
    1
    Views:
    109
    Scott Mitchell [MVP]
    Nov 15, 2004
Loading...

Share This Page