Page 1 of 1

Reading an XML for export into MySQL Database

Posted: Wed Jan 31, 2007 2:15 am
by Mr Tech
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I've got to create a script to import data from an XML file into a MySQL database. Here is some sample XML data:

[syntax="xml"]    <account gm_recid="BS6IJMR+$ ;&<RO" gm_accountno="A6040451231%7R6Q<Bra" >
      <properties>
        <property name="2" db_name="UP2N" >
          <property_string>CMS</property_string>
        </property>
        <property name="2" db_name="UP2P" >
          <property_string>660</property_string>
        </property>
    </account>

      <phone_numbers>
        <phone international="1" type="Fax" source_tbl="CONTACT1" source_fld="FAX" >
          <properties>
            <property name="phone_number" >
              <property_string>12345678</property_string>
            </property>
          </properties>
        </phone>
        <phone international="1" type="Phone1" source_tbl="CONTACT1" source_fld="PHONE1" >
          <properties>
            <property name="phone_number" >
              <property_string>2345678</property_string>
            </property>
            <property name="extension" >
              <property_string>Work</property_string>
            </property>
          </properties>
        </phone>
      </phone_numbers>

      <emails>
        <email primary="1" gm_recid="BSBASDL&1^TJ<RO" >
          <properties>
            <property name="email_address" >
              <property_string>them@theirdomain.com.au</property_string>
            </property>
          </properties>
        </email>
      </emails>

      <websites>
        <website primary="1" gm_recid="BTBS0R9(>UJSQ9$" >
          <properties>
            <property name="web_site" >
              <property_string>http://www.theirdomain.com.au</property_string>
            </property>
          </properties>
        </website>
      </websites>
The import part is easy, it's just reading the XML file... Any tutorials or examples you can point me to?


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

PHP XML parser functions

Posted: Wed Jan 31, 2007 2:33 am
by afbase
try refering to some of the examples in the comments/page here

Posted: Wed Jan 31, 2007 3:25 am
by gavin1996
Using DOM of PHP5 to reading XMLfile


or using file_get_contents(file_name),

Posted: Wed Jan 31, 2007 4:07 am
by Mr Tech
Looks like I've found the script for me.

Code: Select all

<?php
 $p =& new xmlParser();
 $p->parse("/* XML file*/");
 echo "<pre>";
 print_r($p->output);
 echo "</pre>";

class xmlParser{
   var $xml_obj = null;
   var $output = array();
   var $attrs;

   function xmlParser(){
       $this->xml_obj = xml_parser_create();
       xml_set_object($this->xml_obj,$this);
       xml_set_character_data_handler($this->xml_obj, 'dataHandler');
       xml_set_element_handler($this->xml_obj, "startHandler", "endHandler");
   }

   function parse($path){
       if (!($fp = fopen($path, "r"))) {
           die("Cannot open XML data file: $path");
           return false;
       }

       while ($data = fread($fp, 4096)) {
           if (!xml_parse($this->xml_obj, $data, feof($fp))) {
               die(sprintf("XML error: %s at line %d",
               xml_error_string(xml_get_error_code($this->xml_obj)),
               xml_get_current_line_number($this->xml_obj)));
               xml_parser_free($this->xml_obj);
           }
       }

       return true;
   }

   function startHandler($parser, $name, $attribs){
       $_content = array();
       $_content['name'] = $name;
       if(!empty($attribs))
           $_content['attrs'] = $attribs;
       array_push($this->output, $_content);
}

   function dataHandler($parser, $data){
       if(!empty($data) && $data!="\n") {
           $_output_idx = count($this->output) - 1;
           $this->output[$_output_idx]['content'] .= $data;
       }
   }

   function endHandler($parser, $name){
       if(count($this->output) > 1) {
           $_data = array_pop($this->output);
           $_output_idx = count($this->output) - 1;
           $add = array();
           if(!$this->output[$_output_idx]['child'])
               $this->output[$_output_idx]['child'] = array();
           array_push($this->output[$_output_idx]['child'], $_data);
       } 
   }
}
?>
This XML is VERY complex (it was exported from GoldMine) so it's gonna take me a while... ah well, there's nothing like a challenge!

Posted: Wed Jan 31, 2007 4:09 am
by Kieran Huggins
How did you want to store it in the database? just as a text blob?

Posted: Wed Jan 31, 2007 4:19 am
by Mr Tech
Text i guess. Not sure what a text blob is...

Posted: Wed Jan 31, 2007 8:13 pm
by Kieran Huggins
why not just store it in the file system? Seems that's what it's for...

Posted: Wed Jan 31, 2007 8:14 pm
by Mr Tech
I prefer working with databases rather than reading from text files. :)

Posted: Wed Jan 31, 2007 8:50 pm
by Kieran Huggins
I prefer serving web apps off a Mac Classic with a goldfish tennant, but that doesn't make it more practical. Databases weren't designed to store files.... why does everyone insist on doing it?

Don't get me wrong - I've built a DBFS before (and regretted it, but it was a good exercise), and I currently use a db to store file tables....but the files themselves live on the file system.

Posted: Wed Jan 31, 2007 10:04 pm
by Mr Tech
I'm not sure if we are on the same page.

I simply am importing data from an XML file into a MySQL database, into separate tables and fields, so that I can access that data with my PHP script. I'm not storing any files in a database...