PHP - XML - Exporting Data to XML

Turn the result set into an XML document with PHP's DOM functions:

 

1. <?php
2. // open connection
3. $mysqli = new mysqli("localhost", "user", "pass", "db1");
4. if (mysqli_connect_errno()) {
5.     die("ERROR: Cannot connect. " . mysqli_connect_error());
6. }
8. // create and execute SELECT query
9. $sql = "SELECT id, name, price FROM products";
10. if ($result = $mysqli->query($sql)) {
11.     // if results exist
12.     // initialize DOM object
13.     $xml = new DOMDocument("1.0");
15.     // add root node
16.     $root = $xml->createElement("resultset");
17.     $xml->appendChild($root);
19.     // iterate over result set
20.     // print <record>s and <field>s
21.     if ($result->num_rows > 0) {
22.         while($row = $result->fetch_row()) {
23.             $record = $xml->createElement("record");
25.             $root->appendChild($record);
26.             $fieldCount = 0;
27.             while ($fieldCount < $mysqli->field_count) {
28.                 $field = $xml->createElement("field");
29.                 $record->appendChild($field);
30.                 $field->appendChild(↩
31. $xml->createTextNode($row[$fieldCount]));
32.                    $fieldCount++;
33.                }
34.            }
35.     }
36.     $result->close();
37. } else {
38.     die("ERROR: " . $mysqli->error . " (query was $sql)");
39. }
41. // close connection
42. $mysqli->close();
44. // display XML result set as HTML…
45. $xml->formatOutput = true;
46. echo "<xmp>" . $xml->saveXML() . "</xmp>";
48. // …or write it to a file as XML
49. $xml->save("results.xml") or die("ERROR: Could not write to file");
50. ?>

Comments

There are a number of reasons why you might want to export a SQL result set to an XML file: to make the data more usable and portable, to reduce your application's dependence on a database server, or to improve performance (because it's usually faster to read from a disk file than from a network connection).

The previous listing shows you how, by executing a SELECT query and then converting the resulting data set into a series of <record> and <field> elements. PHP's DOM extension, which comes with built-in support for dynamically adding nodes to an XML document tree, takes care of the heavy lifting with its createElement(), createTextNode(), and appendChild() methods (these methods are discussed in greater detail in the listings in "10.4: Creating XML" and "10.5: Adding or Removing XML Nodes"). Once the document has been completely generated, it may be written to a file via the save() method, or returned as a string suitable for display with the saveXML() method.

You can also do the reverse: read data from an XML file and write it to a database. The next listing demonstrates this, using SimpleXML to parse an XML file and generate a series of SQL INSERT statements from it:

 

1. <?php
2. $xmlData =<<< END
3. <?xml version="1.0"?>
4. <data>
5.      <item>
6.           <id>20</id>
7.           <name>mangoes</name>
8.           <price>11</price>
9.      </item>
10.      <item>
11.           <id>22</id>
12.           <name>strawberries</name>
13.           <price>5</price>
14.       </item>
15.       <item>
16.            <id>23</id>
17.            <name>grapes</name>
18.            <price>25</price>
19.        </item>
20.    </data>
21. END;
23. // read XML data string
24. $xml = simplexml_load_string($xmlData)↩
25. or die("ERROR: Cannot create SimpleXML object");
27. // open MySQL connection
28. $connection = mysqli_connect("localhost", "user", "pass", "db1")↩
29. or die ("ERROR: Cannot connect");
31. // process node data
32. // create and execute INSERT queries
33. foreach ($xml->item as $item) {
34.     $id = $item->id;
35.     $name = mysqli_real_escape_string($connection, $item->name);
36.     $price = $item->price;
37.     $sql = "INSERT INTO products (id, name, price)↩
39. VALUES ('$id', '$name', '$price')";
40.     mysqli_query($connection, $sql) or die ("ERROR: " .↩
41. mysqli_error($connection) . " (query was $sql)");
42. }
44. // close connection
45. mysqli_close($connection);
46. ?>

In this listing, SimpleXML is used to iterate over each <item> in the XML document and generate an SQL INSERT statement from the values contained within it. PHP's ext/mysqli functions are then used to execute each INSERT statement on the database server, thus writing the data to the database.

Tip

Metabase is a PHP-based database abstraction layer that uses XML to express table relationships, structures and records. Read more about it at http://www.phpclasses.org/metabase.