Importing XML Data into a Database
| I l @ ve RuBoard |
| Now that you know how to convert database records into well- formed XML documents, let's turn things around to look at how the process works in reverse. This section examines how data marked up with XML can be converted into SQL statements and inserted into a database. Dynamically Constructing SQL Queries from an XML Document with SAXAgain, we'll begin with a simple exampleconsider the XML document in Listing 7.10. Listing 7.10 An XML Book List ( books.xml )<?xml version="1.0"?> <list> <item> <title>Waking Up With The Red-Eyed Bed Monster</title> <author>J. I. M. Somniac</author> <blurb>The blood-chillingly true story of one man's fight against the The process of putting this XML-encoded data into a database involves the following four steps:
It's possible to accomplish these tasks using SAX, XSLT, or the DOM. Most of the time, the process is fairly simple; the level of complexity varies depending on how generic you need your script to be. Listing 7.11 demonstrates the process, using SAX in a script designed specifically to handle the XML document described in Listing 7.10. Listing 7.11 Generating and Executing SQL Statements with SAX <?php // initialize some variables $currentTag = ""; // this array will hold the values for the SQL statement $values = array(); // this array will hold allowed fields/elements $allowedFields = array("title", "author", "blurb"); // XML file to parse $xml_file="books.xml"; // database parameters $host = "localhost"; $user = "john"; $pass = "doe"; $db = "library"; $table = "books"; // called when parser finds start tag function startElementHandler($parser, $name, $attributes) { global $currentTag; $currentTag = $name; } // called when parser finds end tag function endElementHandler($parser, $name) { global $values, $currentTag; // import database link and table name global $connection, $table; // if ending <item> tag // implies end of record if (strtolower($name) == "item") { // generate the query string $query = "INSERT INTO books"; $query .= "(title, author, blurb) "; $query .= "VALUES(\"" . join("\", \"", $values) . "\");"; // uncomment for debug // print $query; // execute query $result = mysql_query($query) or die ("Error in query: $query. " . Again, most of this should already be familiar to you from Chapter 2, but I'll run through the highlights quickly anyway.
Listing 7.12 demonstrates the output of this script. Listing 7.12 A Series of SQL Statements Dynamically Generated from an XML File INSERT INTO books(title, author, blurb) VALUES("Waking Up With The Red-Eyed Bed Monster", Now, Listing 7.11 makes certain assumptions about the field names and the number of values in each SQL statement. These assumptions may not always be true, so let's see if we can develop something a little more generic by using the XML document in Listing 7.13 as the base. Listing 7.13 A More Generic XML Table Representation ( data.xml )<?xml version="1.0"?> <table name="readings"> <record> <a>56</a> <b>1222</b> <c>78.5</c> </record> <record> <x>45</x> <y>-10</y> </record> <record> <x>12</x> <b>10459</b> <a>756</a> <y>9</y> </record> </table> This XML document is a little more generic than the one demonstrated in Listing 7.10. The table name is specified as an attribute of the document element <table> , whereas individual table records are enclosed within <record> elements. Every record contains a series of field names (elements) and field values (character data). The field names within each <record> may be variable, as may be the total number of fields per record. Listing 7.14 builds on the basic concepts demonstrated in Listing 7.11 and the generic XML document described in Listing 7.13 to create a more generic parserone that makes no assumptions about the number of values in each record, and retrieves the field names from the XML data itself (rather than having them hard-coded into the script). Listing 7.14 A More Generic XML-to-SQL Converter <?php // initialize some variables $currentTag = ""; // these two arrays will hold the fields and values for the SQL statement $fields = array(); $values = array(); // XML file to parse $xml_file="data.xml"; // database parameters // get these via user input $host = "localhost"; $user = "john"; $pass = "doe"; $db = "db423"; // called when parser finds start tag function startElementHandler($parser, $name, $attributes) { global $currentTag, $table; $currentTag = $name; // get table name if (strtolower($currentTag) == "table") { $table = $attributes["name"]; } } // called when parser finds end tag function endElementHandler($parser, $name) { global $fields, $values, $count, $currentTag; // import database link and table name global $connection, $table; // if </record> tag // implies end of record if (strtolower($name) == "record") { // generate the query string $query = "INSERT INTO $table"; $query .= "(" . join(", ", $fields) . ")"; $query .= " VALUES(\"" . join("\", \"", $values) . "\");"; // uncomment for debug // print $query; // 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; $currentTag = ""; } } // called when parser finds cdata function characterDataHandler($parser, $data) { global $fields, $values, $currentTag, $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] = $currentTag; // escape quotes with slashes $values[$count] = mysql_escape_string($data); $count++; } } // initialize parser $xml_parser = xml_parser_create(); // turn off whitespace processing xml_parser_set_option($xml_parser,XML_OPTION_SKIP_WHITE, TRUE); // turn on case folding xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, FALSE); // 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($host, $user, $pass) 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) . This is fairly involved, so an explanation is in order:
Listing 7.15 demonstrates the output. Listing 7.15 A Series of SQL Statements Generated from an XML File INSERT INTO readings(a, b, c) VALUES("56", "1222", "78.5"); INSERT INTO readings(x, y) VALUES("45", "-10"); INSERT INTO readings(x, b, a, y) VALUES("12", "10459", "756", "9"); Now, although this is fairly complicated, it works quite well. But don't be fooled into thinking it's the only wayif there's anything this chapter demonstrates, it's that the XML/PHP combination offers more than one way to skin a cat. In this case, cat-skinning technique number two involves using an XSLT stylesheet to generate the query string, with PHP standing by to actually execute the query. Dynamically Constructing SQL Queries from an XML Document with XSLTAs you may already know, XSLT provides an efficient, powerful mechanism to transform an XML document into a new result documenthere, a series of SQL statements. A number of basic XSLT constructsincluding loops , conditional statements, and XPath node testsare available to accomplish this. They are used with great success in Listing 7.16, which lists the complete stylesheet code to perform this transformation. Listing 7.16 An XSLT Stylesheet to Construct SQL INSERT Statements ( sql.xsl )<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!-- output as text, strip whitespace --> <xsl:output method="text" indent="no" /> <xsl:strip-space elements="*"/> <!-- look for the table node --> <xsl:template match="/table" > <!-- iterate through each record --> <xsl:for-each select="record"> <!-- get the table name --> <xsl:text>INSERT INTO </xsl:text> <xsl:value-of select="/table/@name" /> <xsl:text>(</xsl:text> <!-- iterate through child elements and get field names --> <xsl:for-each select="child::*"> <xsl:value-of select="name()" /> <xsl:if test="position() != last()">, </xsl:if> </xsl:for-each> <xsl:text>) VALUES(</xsl:text> <!-- iterate through child elements and get values --> <xsl:for-each select="child::*"> <xsl:text>"</xsl:text> <xsl:value-of select="." /> <xsl:text>"</xsl:text> <xsl:if test="position() != last()"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> <xsl:text>);</xsl:text> </xsl:for-each> </xsl:template> </xsl:stylesheet> Listing 7.17 processes this stylesheet using PHP's XSLT API. Listing 7.17 An XSLT-Based XML-to-SQL Converter <?php // XML file $xml_file = "data.xml"; // XSLT stylesheet $xslt_file = "sql.xsl"; // database parameters $host = "localhost"; $user = "john"; $pass = "doe"; $db = "db423"; // create the XSLT processor $xp = xslt_create() or die("Could not create XSLT processor"); // process the two files to get the desired output if($result = xslt_process($xp, $xml_file, $xslt_file)) { // uncomment for debug // echo $result; // open connection to database $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); mysql_select_db($db) or die ("Unable to select database!"); // split XSLT output into individual queries $queries = explode(";", $result); // execute each query foreach($queries as $query) { if(!empty($query)) { mysql_query($query) or die ("Error in query: $query. " . In this case, the output from the XSL Transformation is equivalent to that in Listing 7.15 a series of SQL statements, returned as a single string. These SQL statements are separated from each other and individually executed via PHP's mysql_query() function.
|
monsters under his bed.</blurb> </item> <item> <title>The Case Of The Hungry Hippopotamus</title> <author>P. I. Hardhat</author> <blurb>A tough private eye is hired to solve the most challenging case of | I l @ ve RuBoard |
