Parse XML file and insert results in MySQL

Question

I am trying to import a XML file into a MySQL database and I am having some issues along the way. This is the code that I have so far:

function convertXML($node){
    foreach($node->Body->Cube as $child)
        {
            echo $child['date'] .",";
                foreach($child->Rate as $child1)
                {
                    echo $response=$child1 . ",";
                }
            echo "<br>";
                   //mysql_query("INSERT INTO table ('date', 'AED', 'AUD', 'BGN', 'BRL') VALUES $response");
        }
}
$row = convertXML($xml);

And this is a sample of my XML file:

<DataSet>
    <Header>
      <PublishingDate>2012-12-04</PublishingDate>
      <MessageType>DR</MessageType>
    </Header>
    <Body>
      <Subject>Reference</Subject>
      <OrigCurrency>RO</OrigCurrency>
       <Cube date="2012-12-03">
          <Rate currency="AED">0.9439</Rate>
          <Rate currency="AUD">3.6275</Rate>
          .....
          <Rate currency="BGN">2.3170</Rate>
       </Cube>
       <Cube date="2012-12-04">
          <Rate currency="AED">0.93129</Rate>
          <Rate currency="AUD">3.3576</Rate>
           ....
          <Rate currency="BGN">2.245/Rate>
       </Cube>
    </Body>
</DataSet>

I can get the results that I am interested (date and the numeric value from the RAte currency child) and print them on the screen but I am having a hard time introducing them into the database. Any help will be greatly appreciated ...

At one point I would like to automate this somehow so I need to have script to run at predefined intervals ... — Chris19
– Chris19, Commented Dec 4, 2012 at 21:15

jcho360 · Accepted Answer · 2012-12-04 21:17:52Z

1

Did you try LOAD XML LOCAL INFILE???

mysql> LOAD XML LOCAL INFILE 'address.xml'
    ->   INTO TABLE person
    ->   ROWS IDENTIFIED BY '<person>';

more information here:

http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

answered Dec 4, 2012 at 21:17

jcho360

3,7491 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse XML file and insert results in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related