What is the easiest way to import XML to MYSQL

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • J Sharman

    What is the easiest way to import XML to MYSQL

    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



  • Spidah

    #2
    Re: What is the easiest way to import XML to MYSQL

    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


    "the web site for web sites"

    "J Sharman" <joe@joesharman .co.uk> wrote in message
    news:bfmqod$d3a $1@titan.btinte rnet.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]


    Comment

    • pittendrigh

      #3
      Re: What is the easiest way to import XML to MYSQL

      "J Sharman" <joe@joesharman .co.uk> wrote in message news:<bfmqod$d3 a$1@titan.btint ernet.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),#poin ts to the root node of tree
      pid int references node(nid),#poin ts 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.

      Comment

      • Christopher Browne

        #4
        Re: What is the easiest way to import XML to MYSQL

        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 hierarchicalizi ng 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').

        [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?

        Comment

        • Nikolai Chuvakhin

          #5
          Re: What is the easiest way to import XML to MYSQL

          "J Sharman" <joe@joesharman .co.uk> wrote in message
          news:<bfmqod$d3 a$1@titan.btint ernet.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

          Comment

          Working...