XML_Query2XML

Generating XML data from SQL queries

Lukas Feiler
lukas.feiler@lukasfeiler.com
Copyright 2006 by Lukas Feiler

Table of Contents

Introduction

XML_Query2XML allows you to transform the records retrieved with one or more SQL SELECT queries into XML data. Very simple to highly complex transformations are supported. Is was written with performance in mind and can handle large amounts of data. No XSLT needed!

Both methods XML_Query2XML::getXML() and XML_Query2XML::getFlatXML() return an instance of DOMDocument. The class DOMDocument is provided by PHP5's built-in DOM API.

Requirements

XML_Query2XML requires

  • PHP5: XML_Query2XML heavily uses the new exception handling and object orientation features.
  • PHP5's built-in DOM API
  • PDO (PHP5's built-in database abstraction class) PEAR DB, PEAR MDB2 or ADOdb.
The following packages are optional:

Migrating from v0.6.x and v0.7.x to v1.x.x

The release 0.8.0 of XML_Query2XML is not backward compatible! Due to security considerations XML_Query2XML does not use the native function eval() anymore. Therefore

Proposed migration strategy:

  • Wherever you currently use the "!" prefix, use the new callback prefix "#" instead. The first argument passed to the callback function/method is always the current record ($record). You can supply additional static arguments by placing them within the braces, e.g. 'MyClass:myMethod(arg2, arg3)' will result in MyClass:myMethod() being called with the current record as the first, the string 'arg2' as the second and 'arg3' as the third argument. In most cases you will want to put whatever code you used after the "!" prefix into a separate function or static method. That function/method is what you call using the callback prefix "#".
  • The migration for $options['condition'] works similarly. Move the PHP code into a separate function/method and call it using the callback prefix "#".

XML_Query2XML::factory()

XML_Query2XML::factory($db)

This is the factory method that will return a new instance of XML_Query2XML. The argument passed to the factory method can be an instance of PDO, PEAR DB, PEAR MDB2, ADOdb, PEAR Net_LDAP, PEAR Net_LDAP2 or any class that extends XML_Query2XML_Driver

Database Drivers for PDO, PEAR MDB2, PEAR DB, ADOdb

XML_Query2XML has drivers for the database abstraction layers PDO, PEAR MDB2, PEAR DB and ADOdb.

Using PDO with XML_Query2XML works like this:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. $pdo = new PDO('mysql://root@localhost/Query2XML_Tests');
  4. $query2xml = XML_Query2XML::factory($pdo);
  5. ?>

Using MDB2 with XML_Query2XML works like this:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'MDB2.php';
  4. $mdb2 = MDB2::factory('mysql://root@localhost/Query2XML_Tests');
  5. $query2xml = XML_Query2XML::factory($mdb2);
  6. ?>

The same thing with PEAR DB looks like that:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'DB.php';
  4. $db = DB::connect('mysql://root@localhost/Query2XML_Tests');
  5. $query2xml = XML_Query2XML::factory($db);
  6. ?>

And again the same thing with ADOdb:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'adodb/adodb.inc.php';
  4. //require_once 'adodb/adodb-exceptions.inc.php';
  5. //require_once 'adodb/adodb-pear.inc.php';
  6. $adodb = ADONewConnection('mysql');
  7. $adodb->Connect('localhost', 'root', '', 'Query2XML_Tests');
  8. $query2xml = XML_Query2XML::factory($adodb);
  9. ?>
Note that XML_Query2XML works with ADOdb with the default error handling (no additional include file), error handling using exceptions (adodb-exceptions.inc.php) and error handling using PEAR_Error (adodb-pear.inc.php).

I would recommend using MDB2 as it can be considered more advanced than DB and much better designed and documented than ADOdb. MDB2 also provides more flexibility than PDO. If you want to access a SQLite 3 database use PDO - MDB2 does only support SQLite 2 as of this writing. But use whichever you like - XML_Query2XML works with all of them. For the sake of simplicity all the examples will use PEAR MDB2.

LDAP Driver for PEAR Net_LDAP

Since v1.6.0RC1 XML_Query2XML comes with a driver for PEAR Net_LDAP. The driver for PEAR Net_LDAP2 is available since v1.7.0RC1.

Using Net_LDAP(2) with XML_Query2XML works like this:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. $ldap = Net_LDAP::connect(
  4.     'host'     => 'ldap.example.com',
  5.     'port'     => 389,
  6.     'version'  => 3,
  7.     'starttls' => true,
  8.     'binddn'   => 'cn=Manager,ou=people,dc=example,dc=com',
  9.     'bindpw'   => 'secret'
  10. );
  11. $query2xml = XML_Query2XML::factory($ldap);
  12. ?>
The driver for Net_LDAP(2) uses a diffrent format for $sql. Instead of a string it expects an associative array with the following elements:
  • 'base': the base search DN
  • 'filter': the query filter that determines which results are returned
  • 'options': an array of configuration options for the current query
More information on how to use the LDAP drivers can be found under The LDAP Driver

XML_Query2XML::getFlatXML()

XML_Query2XML::getFlatXML($sql, $rootTagName = 'root', $rowTagName = 'row')

This method transforms the data retrieved by a single SQL query into flat XML data. Pass the SQL SELECT statement as first, the root tag's name as second and the row tag's name as third argument.

In most cases you will want to use XML_Query2XML::getXML() instead. Please see Case 01: simple SELECT with getFlatXML for an example usage of getFlatXML().

XML_Query2XML::getXML()

XML_Query2XML::getXML($sql, $options)

This method is the most powerful transformation method. It returns an instance of DOMDocument (part of PHP5's built-in DOM API). The records returned by the query/queries will be processed one after another. The $options argument is a rather complex, associative, multi dimensional array. The $sql argument can be a string or as well an associative array.

$sql

This option is almost exactly like $options['sql']: you can specify the query with a Simple Query Specification or a Complex Query Specification. What is different from $options['sql'] is that you can also specify a boolean value of false.

Here is an example of a simple query specification (WARNING: to prevent SQL injection vulerabilities you should use a complex query specification when dealing with non-static queries like this one):

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'MDB2.php';
  4. $query2xml = XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  5. if (isset($_REQUEST['artistid']) && is_numeric($_REQUEST['artistid'])) {
  6.     $artistid =  $_REQUEST['artistid'];
  7. } else {
  8.     $artistid = 1;
  9. }
  10. $dom = $query2xml->getXML(
  11.   "SELECT * FROM artist WHERE artistid = $artistid",
  12.   array(
  13.     'rootTag' => 'favorite_artist',
  14.     'idColumn' => 'artistid',
  15.     'rowTag' => 'artist',
  16.     'elements' => array(
  17.         'name',
  18.         'birth_year',
  19.         'music_genre' => 'genre'
  20.     )
  21.   )
  22. );
  23. header('Content-Type: application/xml');
  24. $dom->formatOutput = true;
  25. print $dom->saveXML();
  26. ?>
With simple query specifications you have to prevent SQL injection yourself. Here I ensured that $artistid is numeric by calling is_numeric().

Next we use a Complex Query Specification and prevent SQL injections by using PDO's/MDB2's/DB's/ADOdb's prepare() and execute() methods.

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'MDB2.php';
  4. $query2xml = XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  5. $artistid = $_REQUEST['artistid'];
  6. $dom = $query2xml->getXML(
  7.   array(
  8.     'data' => array(
  9.         ":$artistid"
  10.     ),
  11.     'query' => 'SELECT * FROM artist WHERE artistid = ?'
  12.   ),
  13.   array(
  14.     'rootTag' => 'favorite_artist',
  15.     'idColumn' => 'artistid',
  16.     'rowTag' => 'artist',
  17.     'elements' => array(
  18.       'name',
  19.       'birth_year',
  20.       'music_genre' => 'genre'
  21.     )
  22.   )
  23. );
  24. header('Content-Type: application/xml');
  25. $dom->formatOutput = true;
  26. print $dom->saveXML();
  27. ?>
The resulting XML data is identical in both cases (given that artistid was submitted as 1):
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artist>
  3.   <artist>
  4.     <name>Curtis Mayfield</name>
  5.     <birth_year>1920</birth_year>
  6.     <music_genre>Soul</music_genre>
  7.   </artist>
  8. </favorite_artist>

As stated above $sql can also be a boolean value of false. This will only be useful in scenarios where you want to combine the results of multiple unrelated queries into a single XML document. XML_Query2XML will deal with an $sql argument that has a value of false as if it executed a query that returned a single record with no colunns.

If you simpy wanted all the records of the table "album" and all the records of the table "artist" you could write code like this:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'MDB2.php';
  4.  
  5. $query2xml = XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  6.  
  7. $dom = $query2xml->getXML(
  8.     false,
  9.     array(
  10.         'idColumn' => false,
  11.         'rowTag' => '__tables',
  12.         'rootTag' => 'music_store',
  13.         'elements' => array(
  14.             'artists' => array(
  15.                 'rootTag' => 'artists',
  16.                 'rowTag' => 'artist',
  17.                 'idColumn' => 'artistid',
  18.                 'sql' => 'SELECT * FROM artist',
  19.                 'elements' => array(
  20.                     '*'
  21.                 )
  22.             ),
  23.             'albums' => array(
  24.                 'rootTag' => 'albums',
  25.                 'rowTag' => 'album',
  26.                 'idColumn' => 'albumid',
  27.                 'sql' => 'SELECT * FROM album',
  28.                 'elements' => array(
  29.                     '*'
  30.                 )
  31.             )
  32.         )
  33.     )
  34. );
  35.  
  36. header('Content-Type: application/xml');
  37. $dom->formatOutput = true;
  38. print $dom->saveXML();
  39. ?>
In this case we actually are not interested in $sql at all; all we want is to get our $options['sql']s executed. Also note that we used '__tables' for $options['rowTag'] at the root level: this is because we don't have anything to loop over at the root level - remember using false for $sql is like using a query that returns a single record with no columns.

The resulting XML looks like this:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <music_store>
  3.   <artists>
  4.     <artist>
  5.       <artistid>1</artistid>
  6.       <name>Curtis Mayfield</name>
  7.       <birth_year>1920</birth_year>
  8.       <birth_place>Chicago</birth_place>
  9.       <genre>Soul</genre>
  10.     </artist>
  11.     <artist>
  12.       <artistid>2</artistid>
  13.       <name>Isaac Hayes</name>
  14.       <birth_year>1942</birth_year>
  15.       <birth_place>Tennessee</birth_place>
  16.       <genre>Soul</genre>
  17.     </artist>
  18.     <artist>
  19.       <artistid>3</artistid>
  20.       <name>Ray Charles</name>
  21.       <birth_year>1930</birth_year>
  22.       <birth_place>Mississippi</birth_place>
  23.       <genre>Country and Soul</genre>
  24.     </artist>
  25.   </artists>
  26.   <albums>
  27.     <album>
  28.       <albumid>1</albumid>
  29.       <artist_id>1</artist_id>
  30.       <title>New World Order</title>
  31.       <published_year>1990</published_year>
  32.       <comment>the best ever!</comment>
  33.     </album>
  34.     <album>
  35.       <albumid>2</albumid>
  36.       <artist_id>1</artist_id>
  37.       <title>Curtis</title>
  38.       <published_year>1970</published_year>
  39.       <comment>that man's got somthin' to say</comment>
  40.     </album>
  41.     <album>
  42.       <albumid>3</albumid>
  43.       <artist_id>2</artist_id>
  44.       <title>Shaft</title>
  45.       <published_year>1972</published_year>
  46.       <comment>he's the man</comment>
  47.     </album>
  48.   </albums>
  49. </music_store>

Want to dump not just two but all of your table? Have a look at Using dynamic $options to dump all data of your database.

$options['elements']

This option is an array that basically holds column names to include in the XML data as child elements. There are two types of element specifications:

Simple Element Specifications

These allow you to use an array to specify elements that have only two properties: a name and a value. The array values are used to specify the XML element values whereas the array keys are used to specify the XML element names. For all array elements that are defined without a key, the array values will be used for the XML element names. If no prefix is used (see below) the contents of the array values are interpreted as column names. The following example illustrates the most basic usage of a simple element specification:

  1. array(
  2.     'COLUMN1',
  3.     'COLUMN2'
  4. );
This might result in XML data like this:
  1. <COLUMN1>this was the contents of COLUMN1</COLUMN1>
  2. <COLUMN2>this was the contents of COLUMN2</COLUMN2>
If you do not want your XML elements named after your database columns you have to work with array keys (ELEMENT1 and ELEMENT2 in our example): while the element specification
  1. array(
  2.     'ELEMENT1' => 'COLUMN1',
  3.     'ELEMENT2' => 'COLUMN2'
  4. );
This would make the same data appear like this:
  1. <ELEMENT1>this was the contents of COLUMN1</ELEMENT1>
  2. <ELEMENT2>this was the contents of COLUMN2</ELEMENT2>

If you use both, the array key and the array value to specify an XML element, the array value can be of the following types:

  • COLUMN NAME (string): this is the default if not preceeded by ':' or '#'. If the column does not exist, an XML_Query2XML_ConfigException will be thrown.
  • STATIC TEXT with a : prefix (string): if the value is preceeded by a colon (':'), it is interpreted as static text.
  • CALLBACK FUNCTION with a # prefix (string): if the value is preceeded by a pound sign ('#'), it is interpreted as a callback function. You can use a regular function (e.g. '#myFunction()') or a static method (e.g. '#MyClass::myFunction()') - for how to use a non-static method, see the type COMMAND OBJECT. The current record will be passed to the callback function as an associative array. You can also pass additional string arguments to the callback function by specifing them within the opening and closing brace; e.g. '#Utils::limit(12)' will result in Util::limit() being called with the current record as the first and '12' as the second argument. If you do not want to pass additional arguments to the callback function, the opening and closing brace are optional. The callback function's return value will be converted to a string and used as the child text node if it is anything but an object or an array. If you do return an object or an array from a callback function it has to be an instance of DOMNode or an array of DOMNode instances. Please see Integrating other XML data sources for examples and further details. If an instances of any other class is returned, a XML_Query2XML_XMLException will be thrown.
  • COMMAND OBJECT (object): If you want to use a non-static method as a callback function, you can do so by specifying the value as an instance of a class that implements the XML_Query2XML_Callback interface. This implementation of the command pattern gives you all the flexibility. The disadvantage ist that you cannot use the XML UNSERIALIZATION prefix or the CONDITIONAL prefix. Note: you have to require_once 'XML/Query2XML/Callback.php' before using the XML_Query2XML_Callback interface. The return value of a COMMAND OBJECT's execute() method is treated exactly the same as the return value of a CALLBACK FUNCTION.
There are four more prefixes available that can be used in conjunction with all the prifixes described above:
  • XML UNSERIALIZATION prefix &: the ampersand (&) prefix allows you to automatically unserialize string data, i.e. transform a string into a DOMDocument. DOMDocument's loadXML() method will be used for this purpose. You can combine all three types with this prefix: '&COLUMN_NAME', '&#function()' or '&:<name>John</name>' will all work. You can even use the CONDITIONAL prefix which has to preceed all other prefixes. If the data cannot be unserialized i.e. DOMDocument::loadXML() returns false, a XML_Query2XML_XMLException will be thrown. Please see Integrating other XML data sources for examples and further details.
  • BASE64 ENCODING prefix ^: if the specification starts with a carrat sign ('^'), the element value will be passed to base64_encode(). The BASE64 ENCODING prefix can be used with all the prefixes described above (just put the BASE64 ENCODING prefix first): e.g. '^#', '^:' or '^COLUMN_NAME'.
  • CDATA SECTION prefix =: if the specification starts with an equal sign ('='), the element value will be enclosed in a CDATA section. A CDATA section starts with "<![CDATA[" and ends with "]]>". The CDATA SECTION prefix can be used with all the prefixes described above (just put the CDATA SECTION prefix first): e.g. '=#', '=:', '=COLUMN_NAME' or '=^'.
  • CONDITIONAL prefix ?: if the specification starts with a question mark ('?'), the whole element will be skipped if the value equals (==) an empty string. The CONDITIONAL prefix can be combined with all types described above: if you do this you have to write the CONDITIONAL prefix first e.g. '?#', '?:', '?&', '?=', '?^', or '?COLUMN_NAME'.
Note: for ovious reasons, the prefix cannot be combined with a COMMAND OBJECT.

Basically, the same syntax can be use for $options['value'], $options['attributes'], Complex Query Specification and $options['idColumn'] because the private method XML_Query2XML::_applyColumnStringToRecord() is used in all cases.

Let's start out with a very simple example. It will use the column name as the XML element name for the first two columns but the custom element name 'music_genre' for the column 'genre':

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'MDB2.php';
  4. $query2xml = XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  5. $dom = $query2xml->getXML(
  6.   "SELECT * FROM artist",
  7.   array(
  8.     'rootTag' => 'favorite_artists',
  9.     'idColumn' => 'artistid',
  10.     'rowTag' => 'artist',
  11.     'elements' => array(
  12.         'name',
  13.         'birth_year',
  14.         'music_genre' => 'genre'
  15.     )
  16.   )
  17. );
  18. header('Content-Type: application/xml');
  19. $dom->formatOutput = true;
  20. print $dom->saveXML();
  21. ?>
This results in the following XML data:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist>
  4.     <name>Curtis Mayfield</name>
  5.     <birth_year>1920</birth_year>
  6.     <music_genre>Soul</music_genre>
  7.   </artist>
  8.   <artist>
  9.     <name>Isaac Hayes</name>
  10.     <birth_year>1942</birth_year>
  11.     <music_genre>Soul</music_genre>
  12.   </artist>
  13.   <artist>
  14.     <name>Ray Charles</name>
  15.     <birth_year>1930</birth_year>
  16.     <music_genre>Country and Soul</music_genre>
  17.   </artist>
  18. </favorite_artists>

The following example demonstrates the usage of all different types:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'XML/Query2XML/Callback.php';
  4. require_once 'MDB2.php';
  5.  
  6. class Utils
  7. {
  8.     function trim($record, $columnName)
  9.     {
  10.         return trim($record[$columnName]);
  11.     }
  12.     
  13.     function getPublishedYearCentury($record)
  14.     {
  15.         return floor($record['published_year']/100);
  16.     }
  17. }
  18.  
  19. class ToLowerCallback implements XML_Query2XML_Callback
  20. {
  21.     private $_columnName = '';
  22.     
  23.     public function __construct($columnName)
  24.     {
  25.         $this->_columnName = $columnName;
  26.     }
  27.     
  28.     public function execute(array $record)
  29.     {
  30.         return strtolower($record[$this->_columnName]);
  31.     }
  32. }
  33.  
  34. $query2xml = XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  35. $dom = $query2xml->getXML(
  36.   "SELECT
  37.     *
  38.    FROM
  39.     sale,
  40.     store,
  41.     album
  42.    WHERE
  43.     sale.store_id = store.storeid
  44.     AND
  45.     sale.album_id = album.albumid
  46.     AND
  47.     sale.timestamp < '2005-06-01'",
  48.   array(
  49.     'rootTag' => 'sales',
  50.     'idColumn' => 'saleid',
  51.     'rowTag' => 'sale',
  52.     'elements' => array(
  53.         'saleid',
  54.         'sale_timestamp' => 'timestamp',
  55.         'static' => ':some static text',
  56.         'now' => ':' . time(),
  57.         'album_century' => '#Utils::getPublishedYearCentury()',
  58.         'album_title' => '?#Utils::trim(title)',
  59.         'album_comment' => new ToLowerCallback('comment'),
  60.         'storeid',
  61.         'store_building1' => '?&building_xmldata',
  62.         'store_building2' => '?=building_xmldata',
  63.         'store_building3' => '?^building_xmldata'
  64.     )
  65.   )
  66. );
  67.  
  68. header('Content-Type: application/xml');
  69. $dom->formatOutput = true;
  70. print $dom->saveXML();
  71. ?>
Let's go through all simple element specifications, one by one:
  • 'saleid': this is as simple as it can get. The value of the column saleid will be used for an element named saleid.
  • 'sale_timestamp' => 'timestamp': here we want to place the value of the column timestamp in an element named sale_timestamp; we therefore use sale_timestamp as the array key.
  • 'static' => ':some static text': the STATIC TEXT (note the ":" prefix) "some static text" will be placed inside an element named static.
  • 'now' => ':' . time(): here the static text is computed at run time; however it will be the same for all "now" elements.
  • 'album_century' => '#Utils::getPublishedYearCentury()': here we use a CALLBACK FUNCTION with a "#" prefix; the return value of Utils::getPublishedYearCentury() is used as the XML element value. Note that the callback function will automatically be called with the current $record as the first argument.
  • 'album_title' => '?#Utils::trim(title)': we also use a CALLBACK FUNCTION with a "#" prefix, but this time we pass an additional string argument to our callback function by specifing it within the opening and closing brace. Also, we use the CONDITIONAL prefix ? which means that the album_title element will only appear in the generated XML data if Utils::trim() returned a non-empty string (to be precise a string that != "").
  • 'album_comment' => new ToLowerCallback('comment'): here we use a COMMAND OBJECT implementing the XML_Query2XML_Callback interface. This is the object oriented way to use callbacks! Note how we pass the column name to the callback class constructor, so that it's execute() method will now what column to work on.
  • 'storeid': plain an simple again
  • 'store_building1' => '?&building_xmldata': here we use the XML UNSERIALIZATION prefix "&" to transform the value of the building_xmldata column into a DOMDocument. Using the CONDITIONAL prefix ? means that store_building1 will only appear if building_xmldata is non-empty (!= "" to be precise).
  • 'store_building2' => '?=building_xmldata': CDATA SECTION prefix "=" is another way incorporate XML data; the contents of the column building_xmldata will be surrounded by "<![CDATA[" and "]]>". Using the CONDITIONAL prefix ? means that store_building2 will only appear if building_xmldata is non-empty (!= "" to be precise).
  • 'store_building3' => '?^building_xmldata': here we use the BASE64 ENCODING prefix "^" to first base64-encode the contents of the building_xmldata column. We again use the CONDITIONAL prefix "?".
The resulting XML data looks like this:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <sales>
  3.   <sale>
  4.     <saleid>1</saleid>
  5.     <sale_timestamp>2005-05-25 07:32:00</sale_timestamp>
  6.     <static>some static text</static>
  7.     <now>1187498966</now>
  8.     <album_century>19</album_century>
  9.     <album_title>New World Order</album_title>
  10.     <album_comment>the best ever!</album_comment>
  11.     <storeid>1</storeid>
  12.     <store_building1>
  13.       <building>
  14.         <floors>4</floors>
  15.         <elevators>2</elevators>
  16.         <square_meters>3200</square_meters>
  17.       </building>
  18.     </store_building1>
  19.     <store_building2>< ![CDATA[<building><floors>4</floors><elevators>2</elevators><square_meters>3200</square_meters></building>]] ></store_building2>
  20.     <store_building3>PGJ1aWxkaW5nPjxmbG9vcnM+NDwvZmxvb3JzPjxlbGV2YXRvcnM+MjwvZWxldmF0b3JzPjxzcXVhcmVfbWV0ZXJzPjMyMDA8L3NxdWFyZV9tZXRlcnM+PC9idWlsZGluZz4=</store_building3>
  21.   </sale>
  22.   <sale>
  23.     <saleid>11</saleid>
  24.     <sale_timestamp>2005-05-25 07:23:00</sale_timestamp>
  25.     <static>some static text</static>
  26.     <now>1187498966</now>
  27.     <album_century>19</album_century>
  28.     <album_title>Curtis</album_title>
  29.     <album_comment>that man's got somthin' to say</album_comment>
  30.     <storeid>2</storeid>
  31.     <store_building1>
  32.       <building>
  33.         <floors>2</floors>
  34.         <elevators>1</elevators>
  35.         <square_meters>400</square_meters>
  36.       </building>
  37.     </store_building1>
  38.     <store_building2>< ![CDATA[<building><floors>2</floors><elevators>1</elevators><square_meters>400</square_meters></building>]] ></store_building2>
  39.     <store_building3>PGJ1aWxkaW5nPjxmbG9vcnM+MjwvZmxvb3JzPjxlbGV2YXRvcnM+MTwvZWxldmF0b3JzPjxzcXVhcmVfbWV0ZXJzPjQwMDwvc3F1YXJlX21ldGVycz48L2J1aWxkaW5nPg==</store_building3>
  40.   </sale>
  41. </sales>
Note: due to a bug in phpDocumentor I had to cheat a little bit in the above XML; as you might have noticed there was a space between "<" and "![CDATA[".


Complex Element Specifications

A complex element specification consists of an array that can have all options that can be present on the root level plus $options['sql'] and $options['sql_options']. This allows for complete (and theoretically infinite) nesting. You will need to use it if the child element should have attributes or child elements.

The following example is like the first one in Simple Element Specifications with one difference: the XML element 'name' should have the attribute 'type' set to the static value 'full_name'. As attributes are not supported by simple elements specifications, we have to use a complex element specification for the element 'name':

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'MDB2.php';
  4. $query2xml = XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  5. $dom = $query2xml->getXML(
  6.   "SELECT * FROM artist",
  7.   array(
  8.     'rootTag' => 'favorite_artists',
  9.     'idColumn' => 'artistid',
  10.     'rowTag' => 'artist',
  11.     'elements' => array(
  12.         'name' => array(
  13.             'value' => 'name',
  14.             'attributes' => array(
  15.                 'type' => ':full_name'
  16.             )
  17.         ),
  18.         'birth_year',
  19.         'music_genre' => 'genre'
  20.     )
  21.   )
  22. );
  23. header('Content-Type: application/xml');
  24. $dom->formatOutput = true;
  25. print $dom->saveXML();
  26. ?>
This results in the following XML data:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist>
  4.     <name type="full_name">Curtis Mayfield</name>
  5.     <birth_year>1920</birth_year>
  6.     <music_genre>Soul</music_genre>
  7.   </artist>
  8.   <artist>
  9.     <name type="full_name">Isaac Hayes</name>
  10.     <birth_year>1942</birth_year>
  11.     <music_genre>Soul</music_genre>
  12.   </artist>
  13.   <artist>
  14.     <name type="full_name">Ray Charles</name>
  15.     <birth_year>1930</birth_year>
  16.     <music_genre>Country and Soul</music_genre>
  17.   </artist>
  18. </favorite_artists>

Here is another little example:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'MDB2.php';
  4. $query2xml = XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  5. $dom = $query2xml->getXML(
  6.     "SELECT * FROM artist LEFT JOIN album ON album.artist_id = artist.artistid",
  7.     array(
  8.         'rootTag' => 'music_library',
  9.         'rowTag' => 'artist',
  10.         'idColumn' => 'artistid',
  11.         'elements' => array(
  12.             'artistid',
  13.             'name',
  14.             'birth_year',
  15.             'birth_place',
  16.             'genre',
  17.             'albums' => array(
  18.               'rootTag' => 'albums',
  19.               'rowTag' => 'album',
  20.               'idColumn' => 'albumid',
  21.               'elements' => array('albumid', 'title', 'published_year')
  22.             )
  23.         )
  24.     )
  25. );
  26. header('Content-Type: application/xml');
  27. $dom->formatOutput = true;
  28. print $dom->saveXML();
  29. ?>
This results in the following XML data:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <music_library>
  3.   <artist>
  4.     <artistid>1</artistid>
  5.     <name>Curtis Mayfield</name>
  6.     <birth_year>1920</birth_year>
  7.     <birth_place>Chicago</birth_place>
  8.     <genre>Soul</genre>
  9.     <albums>
  10.       <album>
  11.         <albumid>1</albumid>
  12.         <title>New World Order</title>
  13.         <published_year>1990</published_year>
  14.       </album>
  15.       <album>
  16.         <albumid>2</albumid>
  17.         <title>Curtis</title>
  18.         <published_year>1970</published_year>
  19.       </album>
  20.     </albums>
  21.   </artist>
  22.   <artist>
  23.     <artistid>2</artistid>
  24.     <name>Isaac Hayes</name>
  25.     <birth_year>1942</birth_year>
  26.     <birth_place>Tennessee</birth_place>
  27.     <genre>Soul</genre>
  28.     <albums>
  29.       <album>
  30.         <albumid>3</albumid>
  31.         <title>Shaft</title>
  32.         <published_year>1972</published_year>
  33.       </album>
  34.     </albums>
  35.   </artist>
  36.   <artist>
  37.     <artistid>3</artistid>
  38.     <name>Ray Charles</name>
  39.     <birth_year>1930</birth_year>
  40.     <birth_place>Mississippi</birth_place>
  41.     <genre>Country and Soul</genre>
  42.     <albums />
  43.   </artist>
  44. </music_library>

As we want for every artist only a single tag we need to identify each artist by the primary key of the table artist. Note that there is a second record for Curtis Mayfield (related to the album Curtis), but we don't want something like

  1. <artist>
  2.   <name>Curtis Mayfield</name>
  3.   <album>
  4.     <name>New World Order</name>
  5.   </album>
  6. </artist>
  7. <artist>
  8.   <name>Curtis Mayfield</name>
  9.   <album>
  10.     <name>Curits</name>
  11.   </album>
  12. </artist>
but rather
  1. <artist>
  2.   <name>Curtis Mayfield</name>
  3.   <albums>
  4.     <album>
  5.      <name>New World Order</name>
  6.     </album>
  7.     <albums>
  8.      <name>Curtis