Importing XML Data to a Database

Importing XML Data to a Database

In addition to exporting database records to XML documents, PHP lets you import data from an XML document into a database. Using the DOM and SAX approach, you can construct SQL queries and insert data into a database from an XML document. To import XML data into a database:

1. Parse the XML data using the DOM or the SAX approach.

2. Create a list of field value pairs.

3. Insert the field value pairs in the database.

4. Run the database query to test whether data is inserted or not and close the database connection.

Connecting to a Database to Import Data

You need to establish a connection with the MySQL database to import data from an XML document to the database. The following code shows how to connect to the MySQL database:

$connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");

In the above code, a connection to the database is established using the mysql_connect() function. You need to specify the hostname, username, and password as arguments to the mysql_connect() function.

Parsing an XML Document

To import XML document into a database, you need to parse the XML document. You can parse the XML document using both the SAX and DOM approach. For example, to import the XML document, student.xml file, that stores the student information in a database, you need to parse the student.xml file.

Listing 8-8 shows the contents of the student.xml file:

Listing 8-8: Content of the student.xml File

<?xml version="1.0" encoding="UTF-8"?>
<list>
<item>
<name>John</name>
<age>15</age>
<address>New York</address>
<standard>10</standard>
</item>
<item>
<name>Joseph</name>
<age>16</age>
<address>New York</address>
<standard>12</standard>
</item>
<item>
<name>Mary</name>
<age>14</age>
<address>New Jersey</address>
<standard>8</standard>
</item>
</list> 

The above listing shows the content of the student.xml file that stores student information, such as name, age, address, and standard.

You need to create a table in the database that can store the data imported from the student.xml file.

Listing 8-9 shows how to create the student table in the information database:

Listing 8-9: Creating the Student Table

use information
mysql> CREATE TABLE student
-> (
-> name Varchar(30) NOT NULL,
-> age Integer NOT NULL,
-> address Varchar(30) NOT NULL,
-> standard Integer
-> );

The above listing creates the student table, in which you can insert data from the student.xml file.

Listing 8-10 shows how to import the student.xml file into a database, by parsing the student.xml file using the SAX approach:

Listing 8-10: Parsing the student.xml File using SAX

<?php	
$cTag = "";
// Array to hold the values for the SQL statement.
$values = array();
$elements = array("name", "age", "address", "standard");
// XML file to parse
$xmlFile = "student.xml";
// Database parameters
$hostname = "localhost";
$username = "root";
$password = "root123";
$database = "information";
$table = "student";
// Processes on encountering a opening tag in the XML. 
function startElementHandler($parser, $n1, $attributes)
{
   global $cTag;
   $cTag = $n1;
}
// Processes on encountering a closing tag in the XML.
function endElementHandler($parser, $n1)
{
   global $values, $cTag;
   // Import database link and table name.
   global $connection, $table;
   // if ending <item> tag
   // Implies end of record.
   if (strtolower($n1) == "item")
   {
      // Generating the query string.
      $query = "INSERT INTO student";
      $query .= "(name, age, address, standard) ";
      $query .= "VALUES(/"" . join("/", /"", $values) . "/");";
      // Processing the query.
      $result = mysql_query($query) or die ("Error in query: $query. " .
      mysql_error());
      // Reset all internal counters and arrays.
      $values = array();
      $cTag = "";
   }
}
// Processes on encountering a closing tag in the XML.
function characterDataHandler($parser, $data)
{
    global $cTag, $values, $elements;
   // lowercase tag name
   $cTag = strtolower($cTag);
   // Look for tag in $elements[] array.
   if (in_array($cTag, $elements) && trim($data) != "")
         $values[$cTag] = mysql_escape_string($data);
}
// Initializing the SAX parser.
$xml_parser = xml_parser_create();
// Set callback functions.
xml_set_element_handler($xml_parser, "startElementHandler", "endElementHandler");
xml_set_character_data_handler($xml_parser, "characterDataHandler");
// Open connection to database.
$connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect!");
mysql_select_db($database) or die ("Unable to select database!");
// read XML file
if (!($fp = fopen($xmlFile, "r")))
{
   die("File I/O error: $xmlFile");
}
// parse XML
while ($data = fread($fp, 4096))
{
   // error handler
   if (!xml_parse($xml_parser, $data, feof($fp)))
   {
      $error_code = xml_get_error_code($xml_parser);
      die("XML parser error (error code " . $error_code . "): " .
      xml_error_string($error_code) . "<br>Error occurred at line " .
      xml_get_current_line_number($xml_parser));
   }
}
?>

The above listing shows how to parse the student.xml file using the SAX approach. In the above listing:

• The instance of the SAX parser is initialized using the xml_parser_create() function, and is configured to call various functions, such as startElemenHandler() and endElementHandler().

• The startElemenHandler() function executes when a starting tag is processed in the XML document, and the endElementHandler() function executes when the closing tag in the XML document is processed.

You need to pass the tag name and the parser as arguments to the tag handler functions, such as startElemenHandler() and endElementHandler(). The characterDataHandler() function executes when the character data in the XML document is processed. You need to specify the Character Data (CDATA) text as arguments in the characterDataHandler() function.

The SAX parser retrieves and stores data from the student.xml file in the associative array, $values. The SAX parser retrieves data after finding character data having element name, such as name, age, address, and standard. The SAX parser creates a query string from the values obtained from the $values array.

You can also parse the XML document using the DOM approach.

Listing 8-11 shows how to parse the XML document using DOM:

Listing 8-11: Parsing XML Document using DOM

<?php
$xml_file = "student.xml";
$doc = xmldocfile($xml_file);
$name = array();
$age = array();
$address = array();
$standard = array();
$root = $doc->root();
$nodes = $root->children();
$a=0;
$b=0;
$c=0;
for($x=0;$x<sizeof($nodes);$x++)
{
   if ($nodes[$x]->type==XML_ELEMENT_NODE)
   {
      $text=$nodes[$x]->children();
      for($i=0;$i<sizeof($text);$i++)
      {
         $temp=$text[$i]->children();
         for($j=0;$j<sizeof($temp);$j++)
         {
            echo $temp[$j]->content;
         }
      }
   }
}
?>

The above listing shows how to parse the student.xml file using the DOM approach.

Closing the Connection to a Database After Importing Data

You need to close the database connection after importing the XML document in the database. The code to close the database connection is:

xml_parser_free($xml_parser);
mysql_close($connection);

The above code shows how to close the connection to the database using the mysql_close() function. The xml_parser_free() function frees the parser and returns true if $xml_parser is valid otherwise returns the value, False.

Alternative Method to Import XML Documents

Using PHP, you can import XML documents in a database without specifying the table name, field names, and values in the SQL statement. For example, you want to import the datastudent.xml file into a table, stud.

Listing 8-12 shows the contents of the datastudent.xml file:

Listing 8-12: Contents of datastudent.xml File

<?xml version="1.0" encoding="UTF-8"?>
<table name1="stud">
<record>
<age>12</age>
<address>New York</address>
<standard>12</standard>
<name>Mary</name>
</record>
<record>
<age>14</age>
<address>New York</address>
<standard>10</standard>
<name>John</name>
</record>
<record>
<age>15</age>
<address>New Jersey</address>
<standard>12</standard>
<name>Tom</name>
</record>
</table>

The above listing stores student information, such as name, record, address, age, and standard. The name of the table, in which the contents of the datastudent.xml file are imported, is specified as an attribute to the <table> element of the XML document. The student information, such as name, address, standard, and age, are specified with the <record> element.

Listing 8-13 shows how to import an XML document in a database without specifying the table and field names:

Listing 8-13: Importing the XML Document

<?php
// Initialize some variables
$cTag = "";
$fields = array();
$values = array();
// XML file to parse
$xml_file="datastudent.xml";
// Database parameters
// Get these via user input
$hostname = "localhost";
$username = "root";
$password = "";
$db = "information";
// Called when parser finds start tag.
function startElementHandler($parser, $name1, $attributes)
{
   global $cTag, $table;
   $cTag = $name1;
   // Get table name.
   if (strtolower($cTag) == "table")
   {
      foreach ($attributes as $v)
      {
         $table=$v;
      }
   }
}
// Called when parser finds end tag.
function endElementHandler($parser, $name1)
{
   global $fields, $values, $count, $cTag;
   // Import database link and table name.
   global $connection, $table;
    if (strtolower($name1) == "record")
    {
      // Generate the query string.
      $query = "INSERT INTO $table";
      $query .= "(" . join(", ", $fields) . ")";
      $query .= " VALUES(/"" . join("/", /"", $values) . "/");";
      // Execute query
      mysql_query($query) or die ("Error in query: $query. " .mysql_error());
      // Reset all internal counters and arrays.
      $fields = array();
      $values = array();
      $count = 0;
      $cTag = "";
    }
 }
// Called when parser finds cdata
function characterDataHandler($parser, $data)
{
   global $fields, $values, $cTag, $count;
   if (trim($data) != "")
   {
      // Add field-value pairs to $fields and $values array.
      // The index of each array is used to correlate the field-value pairs.
      $fields[$count] = $cTag;
      // Escape quotes with slashes
      $values[$count] = mysql_escape_string($data);
      $count++;
   }
}
// Initialize parser
$xml_parser = xml_parser_create();
// Set callback functions.
xml_set_element_handler($xml_parser, "startElementHandler", "endElementHandler");
xml_set_character_data_handler($xml_parser, "characterDataHandler");
// Open connection to database.
$connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
// read XML file
if (!($fp = fopen($xml_file, "r")))
{
   die("File I/O error: $xml_file");
}
// Parse XML
while ($data = fread($fp, 4096))
{
   // Error handler
   if (!xml_parse($xml_parser, $data, feof($fp)))
   {
      $ec = xml_get_error_code($xml_parser);
      die("XML parser error (error code " . $ec . "): " . xml_error_string($ec) .
      "<br>Error occurred at line " . xml_get_current_line_number($xml_parser));
   }
}
// All done, clean up!
xml_parser_free($xml_parser);
mysql_close($connection);
?>

The above listing imports the datastudent.xml file without specifying the table name and field name values in the PHP script. You need to specify the hostname, username, and password of the MySQL database to connect to the database.

In the above listing, the xml_parser_create() function initializes the SAX parser, and the SAX parser processes various handler functions, such as xml_set_elemet_handler and xml_set_character_data_handler.

Figure 8-7 shows the output of Listing 8-13: