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:
-
Parse the XML data using the DOM or the SAX approach.
-
Create a list of field value pairs.
-
Insert the field value pairs in the database.
-
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:
分享到:
相关推荐
The 11th edition of Database Processing refines the organization and content of this classic textbook to reflect a new teaching and professional workplace environment. The organization and topic ...
You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and ...
TADO_QImport3Access Import data from MS Access database TQImport3HTML Intended for importing tables from HTML pages TQImport3XMLDoc Import data from generic XML files using XPath and DataLocation ...
TADO_QImport3Access Import data from MS Access database TQImport3HTML Intended for importing tables from HTML pages TQImport3XMLDoc Import data from generic XML files using XPath and DataLocation ...
Database Tour and Database Tour Pro are cross-database tools with large set of db tools and utilities like SQL tool, reporting tool, data converting, exporting, importing utilities and much more. ...
8.12 Accessing a JDBC Database from a Jython Servlet 8.13 Module: jet2sql-Creating a SQL DDL from an Access Database 9. User Interfaces 9.1 Avoiding lambda in Writing Callback Functions 9.2 ...
Adapting a File-like Object to a True File Object Recipe 2.16. Walking Directory Trees Recipe 2.17. Swapping One File Extension for Another Throughout a Directory Tree Recipe 2.18. Finding a ...
Deciding When to Use DBM and When to Use a Relational Database 255 Working with Relational Databases 255 Writing SQL Statements 257 Defining Tables 259 Setting Up a Database 260 Try It Out: ...
10.2.5. Using profiles to select a data source 10.3. Using JDBC with Spring 10.3.1. Tackling runaway JDBC code 10.3.2. Working with JDBC templates 10.4. Summary Chapter 11. Persisting data with object...
Connection to a Production Database 29.1.3. Connection to a JNDI DataSource 29.2. Using JdbcTemplate 29.3. JPA and “Spring Data” 29.3.1. Entity Classes 29.3.2. Spring Data JPA Repositories 29.3.3. ...
Learn how to use custom plugins and themes, retrieve data, maintain security, use social media, and modify your blog without changing any core code. You'll even get to know the ecosystem of products ...
XMLData property ............................................................ 35 FileName property............................................................ 35 XML property.............................
Importing your WordPress database 197 Uploading your WordPress files 198 Editing your WordPress config file 199 Man the hatches—essential site security 200 User passwords 200 Rename or replace ...
PEP 523: Adding a frame evaluation API to CPython PYTHONMALLOC environment variable DTrace and SystemTap probing support Other Language Changes New Modules secrets Improved Modules array ast ...
18)..Fixed: Possible "Unit XYZ was compiled with a different version of ABC" when using packages 19)..Fixed: FastMM shared MM compatibility 20)..Fixed: Minor bugs in stack tracing (which usually ...