XML_Query2XML

Generating XML data from SQL queries

Lukas Feiler
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</name>
  9.     </albums>
  10.   </albums>
  11. </artist>
This is achieved by telling XML_Query2XML which entity to focus on (on this level): the artist, as it is identified by the artist table's primary key. Once XML_Query2XML get's to the second Curtis Mayfield record, it can tell by the artistid 1 that an XML element was already created for this artist.

For a one more example and a detailed explanation of complex child elements that have child elements themselves, see Case 02: LEFT OUTER JOIN. For an advanced example, see Case 05: three LEFT OUTER JOINs.


Using the Asterisk Shortcut

The asterisk shortcut only works with Simple Element Specifications (and Simple Attribute Specifications).

In some scenarios you will just want to use all columns found in the result set for Simple Element Specifications. This is where the asterisk shortcut can come in very handy. An element specification that contains an asterisk (an "asterisk element specification") will be duplicated for each column present in the result set ($record). The simplest way of using the asterisk shortcut is this:

  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.         '*'
  13.     )
  14.   )
  15. );
  16. header('Content-Type: application/xml');
  17. $dom->formatOutput true;
  18. print $dom->saveXML();
  19. ?>
As the result set contains the column artistid, name, birth_year, birth_place and genre the XML data will look like this:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  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.   </artist>
  10.   <artist>
  11.     <artistid>2</artistid>
  12.     <name>Isaac Hayes</name>
  13.     <birth_year>1942</birth_year>
  14.     <birth_place>Tennessee</birth_place>
  15.     <genre>Soul</genre>
  16.   </artist>
  17.   <artist>
  18.     <artistid>3</artistid>
  19.     <name>Ray Charles</name>
  20.     <birth_year>1930</birth_year>
  21.     <birth_place>Mississippi</birth_place>
  22.     <genre>Country and Soul</genre>
  23.   </artist>
  24. </favorite_artists>
This is because internally, the array
  1. 'elements' => array(
  2.   '*'
  3. )
is expanded to
  1. 'elements' => array(
  2.   'artistid',
  3.   'name',
  4.   'birth_year',
  5.   'birth_place',
  6.   'genre'
  7. )

Think of the asterisk as a variable that will get replaced with each column name found in the result set:

  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.         'TAG_*' => '#padWithHyphens(*)'
  13.     )
  14.   )
  15. );
  16. header('Content-Type: application/xml');
  17. $dom->formatOutput true;
  18. print $dom->saveXML();
  19.  
  20. function padWithHyphens($record$columnName)
  21. {
  22.     return '--' $record[$columnName'--';
  23. }
  24. ?>
The above code would result in the following data:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist>
  4.     <TAG_artistid>--1--</TAG_artistid>
  5.     <TAG_name>--Curtis Mayfield--</TAG_name>
  6.     <TAG_birth_year>--1920--</TAG_birth_year>
  7.     <TAG_birth_place>--Chicago--</TAG_birth_place>
  8.     <TAG_genre>--Soul--</TAG_genre>
  9.   </artist>
  10.   <artist>
  11.     <TAG_artistid>--2--</TAG_artistid>
  12.     <TAG_name>--Isaac Hayes--</TAG_name>
  13.     <TAG_birth_year>--1942--</TAG_birth_year>
  14.     <TAG_birth_place>--Tennessee--</TAG_birth_place>
  15.     <TAG_genre>--Soul--</TAG_genre>
  16.   </artist>
  17.   <artist>
  18.     <TAG_artistid>--3--</TAG_artistid>
  19.     <TAG_name>--Ray Charles--</TAG_name>
  20.     <TAG_birth_year>--1930--</TAG_birth_year>
  21.     <TAG_birth_place>--Mississippi--</TAG_birth_place>
  22.     <TAG_genre>--Country and Soul--</TAG_genre>
  23.   </artist>
  24. </favorite_artists>

You can also combine a simple element specification containing an asterisk shortcut with other (simple and complex) element specifications. The additional element specifications will be treated as an exception to the general rule set up by the asterisk element specification. The following code will produce a tag for each column in the result set containing the column's value. The only exeption is the column "genre" which we want to be different: the value should be all uppercase:

  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.         '*' => '*',
  13.         'genre' => '#genre2uppercase()'
  14.     )
  15.   )
  16. );
  17. header('Content-Type: application/xml');
  18. $dom->formatOutput true;
  19. print $dom->saveXML();
  20.  
  21. function genre2uppercase($record)
  22. {
  23.     return strtoupper($record['genre']);
  24. }
  25. ?>
The resulting XML data looks like this:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  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.   </artist>
  10.   <artist>
  11.     <artistid>2</artistid>
  12.     <name>Isaac Hayes</name>
  13.     <birth_year>1942</birth_year>
  14.     <birth_place>Tennessee</birth_place>
  15.     <genre>SOUL</genre>
  16.   </artist>
  17.   <artist>
  18.     <artistid>3</artistid>
  19.     <name>Ray Charles</name>
  20.     <birth_year>1930</birth_year>
  21.     <birth_place>Mississippi</birth_place>
  22.     <genre>COUNTRY AND SOUL</genre>
  23.   </artist>
  24. </favorite_artists>
This is because internally, the array
  1. 'elements' => array(
  2.   '*' => '*',
  3.   'genre' => '#genre2uppercase()'
  4. )
is expanded to
  1. 'elements' => array(
  2.   'artistid',
  3.   'name',
  4.   'birth_year',
  5.   'birth_place',
  6.   'genre' => '#genre2uppercase()'
  7. )
Please keep in mind that this also applies when combining an asterisk element specification with a complex element specification. That's why the following code would produce exactly the same XML data:
  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.         '*' => '*',
  13.         'genre' => array(
  14.             'value' => '#genre2uppercase()'
  15.         )
  16.     )
  17.   )
  18. );
  19. header('Content-Type: application/xml');
  20. $dom->formatOutput true;
  21. print $dom->saveXML();
  22.  
  23. function genre2uppercase($record)
  24. {
  25.     return strtoupper($record['genre']);
  26. }
  27. ?>

If we wanted to include all columns in the XML output except "genre" we could use a little trick:

  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.         '*' => '*',
  13.         'genre' => '?:'
  14.     )
  15.   )
  16. );
  17. header('Content-Type: application/xml');
  18. $dom->formatOutput true;
  19. print $dom->saveXML();
  20. ?>
In the resulting XML data the column "genre" is missing because we used the CONDITIONAL prefix '?' in combination with a static empty text:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist>
  4.     <artistid>1</artistid>
  5.     <name>Curtis Mayfield</name>
  6.     <birth_year>1920</birth_year>
  7.     <birth_place>Chicago</birth_place>
  8.   </artist>
  9.   <artist>
  10.     <artistid>2</artistid>
  11.     <name>Isaac Hayes</name>
  12.     <birth_year>1942</birth_year>
  13.     <birth_place>Tennessee</birth_place>
  14.   </artist>
  15.   <artist>
  16.     <artistid>3</artistid>
  17.     <name>Ray Charles</name>
  18.     <birth_year>1930</birth_year>
  19.     <birth_place>Mississippi</birth_place>
  20.   </artist>
  21. </favorite_artists>
The exact same result could of course also be achieved using the "condition" option:
  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.         '*' => '*',
  13.         'genre' => array(
  14.             'condition' => '#returnFalse()'
  15.             //this would also work: 'condition' => ':'
  16.         )
  17.     )
  18.   )
  19. );
  20. header('Content-Type: application/xml');
  21. $dom->formatOutput true;
  22. print $dom->saveXML();
  23.  
  24. function returnFalse()
  25. {
  26.     return false;
  27. }
  28. ?>

Another example of how to use the asterisk shortcut can be found in Case 07: Case 03 with Asterisk Shortcuts.

One final note on the asterisk shortcut: if you explicitly specify a tag name (an array element key) it has to contain an asterisk. The following code would cause a XML_Query2XML_ConfigException to be thrown:

  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.         'tag' => '*'
  13.     )
  14.   )
  15. );
  16. header('Content-Type: application/xml');
  17. $dom->formatOutput true;
  18. print $dom->saveXML();
  19. ?>
This is because expanding
  1. 'elements' => array(
  2.   'tag' => '*'
  3. )
to
  1. 'elements' => array(
  2.   'tag' => 'artistid',
  3.   'tag' => 'name',
  4.   'tag' => 'birth_year',
  5.   'tag' => 'birth_place',
  6.   'tag' => 'genre'
  7. )
just makes no sense and therfore "*" is treated as a regular column name - which does not exist in this case! The exception's message would read: [elements]: The column "*" used in the option "tag" does not exist in the result set.


$options['idColumn']

In most cases this will be the name of the column by which a record is identified as unique, aka the primary key. This is especially important within a Complex Element Specification. See there for an example. This option is obligatory at the root level! The idColumn specification can be of the following types:

  • COLUMN NAME: this is the default if not preceeded by ':' or '#'. If the column does not exist, an XML_Query2XML_ConfigException will be thrown. The current record (not the one of the parent level) will be used.
  • STATIC TEXT with a : prefix: if the value is preceeded by a colon (':'), it is interpreted as static text.
  • CALLBACK FUNCTION with a # prefix: 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.
  • 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. 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.
  • FALSE (boolean): Only use this if you don't have a primary key (which is a very bad idea) or you have a very simple tasks at hand like retrieving all records from a table. Using the value FALSE will make XML_Query2XML treat every record as unique. WARNING: it is considered very bad practice to use a value of FALSE if you have a way to specify your primar key. This is because your code might change over time and having your primary key specified will just make your more stable. For a legitimate use of the value FALSE for the option idColumn, please see Using dynamic $options to dump all data of your database.
The same syntax (with the additional '?' prefix but without the boolean value FALSE) can be use for $options['value'], Simple Attribute Specifications, Complex Query Specification and Simple Element Specifications because the private method XML_Query2XML::_applyColumnStringToRecord() is used in all cases.

For example and further discussion of $options['idColumn'] please see Case 02: LEFT OUTER JOIN.

Handling Multi-Column Primary Keys

Sometimes your primary key will consist of multiple columns. For example, this might be the case when you implement a many-to-many relationship using an intersection table.

But as you know by now, $options['idColumn'] has to evaluate to one unique ID for every record. Depending on the type of the primary key columns you will want to choose a different strategy to compute that unique ID for every record. To begin with, you have to choose whether you want to compute that unique ID within the database or using PHP. To do it within the database you will have to define an alias using the "... AS alias_name" syntax. Using PHP you have to use a callback function to generate the ID. When generating the ID, you again have different options.

If your primary key columns are of a numeric type, you can

  • Concatenate your primary key columns using a separator. In the database you would use something like "CONCAT(column1, '_', column2) AS id" and when implemented in PHP you would write a callback that returns something like "$record['column1'] . '_' . $record['column2']".
  • If Leftshift the first column by the number of bits the second column consumes and OR the leftshifted first column with the second column. E.g. if you had two columns defined as TINYINT UNSIGNED (0 to 255, i.e. 8 bits), you could generate the ID by
    1. $id ($column1 << 8$column2;
    Think of like this:
    $column1 (set to 255):           00000000000000000000000011111111
    $column2 left-shifted by 8 bits: 00000000000000001111111100000000
    
    now we OR the left-shifted $column1 with $column2 (both were
    originally set to the maximum of 255):
    $column1 (left-shifted 255): 00000000000000001111111100000000
    $column2 (set to 255):       00000000000000000000000011111111
    -------------------------------------------------------------
    result:                      00000000000000001111111111111111
    As you can see two different combinations of $column1 and $column2
    will always result in a different ID. This is because the left-shifted
    $column1 does not intersect with $column2.
           
    WARNING: an integer in PHP has 32 bits - the tecnique described above therefore only works if the sum of the bits consumed by your primary key columns is less than or equal to 32 (i.e. two 16 bit numbers, four 8 bit numbers or two 8 bit numbers and one 16 bit number).
  • Use the generateIdFromMultiKeyPK() method described below.

If your primary key columns are of a character type (e.g. CHAR, VARCHAR) you have to come up with something else. Before you read on, I strongly urge you to reconsider your choice for the primary key (does it really meet the requirements of minimality and stability, i.e. is immutable?). SECURITY WARNING: Do not simply concatenate your character type columns (with or without a separator). The following example shows why:

record1: column1='a_b' column2='c'
record2: column1='a'   column2='b_c'
When using the separator '_' both records would have an ID of 'a_b_c'.
     
A malicious attacker could use your separator within one of the column values to force ID collisions, which potentially lead to an exploitable security vulnerability. Great care should therefore be taken when choosing a separator - and relying on its confidentiality is not a good strategy. What you might do is to use a separator that is longer than the maximum character length of your primary key columns. But this only makes sense if that maximum is rather low. For example, if you have two CHAR(2) columns, it is reasonable to use the separator '---' which is three characters long.

Another thing one might think of is to use a hash function like sha1() or md5(). But that's not really an option as it would really kill the performance of your application.

The most bullet proof solution to the problem of generating a unique ID from two character type columns is to use a callback function that works with an array. The following function can be used as a callback whenever you need to generate an ID from two character type columns.

  1. <?php
  2. /**Returns a unique ID base on the values stored in
  3. * $record[$columnName1] and $record[$columnName2].
  4. * @param array $record An associative array.
  5. * @param string $columnName1 The name of the first column.
  6. * @param string $columnName2 The name of the second column.
  7. * @return int The ID.
  8. */
  9. function generateIdFromMultiKeyPK($record$columnName1$columnName2)
  10. {
  11.     static $ids array();
  12.     static $idCounter 0;
  13.     
  14.     $column1 $record[$columnName1];
  15.     $column2 $record[$columnName2];
  16.     if (!isset($ids[$column1])) {
  17.         $ids[$column1array();
  18.     }
  19.     if (!isset($ids[$column1][$column2])) {
  20.         $ids[$column1][$column2$idCounter++;
  21.     }
  22.     return $ids[$column1][$column2];
  23. }
  24. ?>

All you have to do is to specify $options['idColumn'] as:

'#generateIdFromMultiKeyPK(name_of_column1, name_of_column2)'
     
Remember: $record is automatically passed as the first argument to the callback function.


$options['attributes']

This option is an array that holds columns to include in the XML data as attributes. Simple and complex attribute specifications are supported.

If you want to add attributes to the root element (i.e. the first child of the DOMDocument instance returned by getXML()), please see Modifying the returned DOMDocument instance.

Simple Attribute Specifications

It works like Simple Element Specifications: the column names are the array values. By default the column's value will be put into an attribute named after the column. If you're unhappy with the default you can specify an other attribute name by using it as the array key. As documented for Simple Element Specifications the prefixes "?", "#", "^" and ":" or a COMMAND OBJECT can be used. Only the UNSERIALIZATION prefix & and the CDATA SECTION prefix ^ which are valid for a Simple Element Specification cannot be used for a Simple Attribute Specification.

The follwing example will use the column name as the attribute name for the first two columns but the custom attribute 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.     'attributes' => array(
  13.       'name',
  14.       'birth_year',
  15.       'music_genre' => 'genre'
  16.     )
  17.   )
  18. );
  19. header('Content-Type: application/xml');
  20. $dom->formatOutput true;
  21. print $dom->saveXML();
  22. ?>
This results in the following XML data:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist name="Curtis Mayfield" birth_year="1920" music_genre="Soul"/>
  4.   <artist name="Isaac Hayes" birth_year="1942" music_genre="Soul"/>
  5.   <artist name="Ray Charles" birth_year="1930" music_genre="Country and Soul"/>
  6. </favorite_artists>


Complex Attribute Specifications

A complex attribute specification consists of an array that must contain

  • $options['value']: the attribute's value (note: you cannot use the UNSERIALIZATION prefix & or the the CDATA SECTION prefix ^ for an attribute specification)
and optionally can contain The array key used to store the complex attribute specification is always used as the attribute's name. Unlike Complex Element Specifications complex attribute specifications cannot be nested for obvious reasons. Complex attribute specifications should only be used for the following reasons:
  • the attribute is only to be included under a condition that cannot be expressed using the '?' prefix within a simple attribute specification
  • additional data is needed from the database
In all other cases Simple Attribute Specifications should be used as they will make your code run faster.

To add a "bornBefore1940" attribute only to those artists that were born before 1940 we could write:

  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.     'attributes' => array(
  12.       'name',
  13.       'birth_year',
  14.       'bornBefore1940' => array(
  15.         'value' => ':true',
  16.         'condition' => '#lessThan(birth_year, 1940)'
  17.       )
  18.     )
  19.   )
  20. );
  21. header('Content-Type: application/xml');
  22. $dom->formatOutput true;
  23. print $dom->saveXML();
  24.  
  25. function lessThan($record$columnName$num)
  26. {
  27.     return $record[$columnName$num;
  28. }
  29. ?>
This results in the following XML data:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist birth_year="1920" bornBefore1940="true" name="Curtis Mayfield" />
  4.   <artist birth_year="1942" name="Isaac Hayes" />
  5.   <artist birth_year="1930" bornBefore1940="true" name="Ray Charles" />
  6. </favorite_artists>

In the next example we want a "firstAlbumTitle" attribute for each artist. For the purpose of the example we will not use a single left outer join but a complex attribute specification with the "sql" option. As retrieving more than one record for a single attribute makes no sense $options['sql_options']['single_record'] is always automatically set to true when fetching records for attributes.

  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.     'attributes' => array(
  12.       'name',
  13.       'birth_year',
  14.       'firstAlbumTitle' => array(
  15.         'value' => 'title',
  16.         'sql' => array(
  17.           'data' => array(
  18.             'artistid'
  19.           ),
  20.           'query' => "SELECT * FROM album WHERE artist_id = ? ORDER BY published_year"
  21.         )
  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. <favorite_artists>
  3.   <artist birth_year="1920" firstAlbumTitle="Curtis" name="Curtis Mayfield" />
  4.   <artist birth_year="1942" firstAlbumTitle="Shaft" name="Isaac Hayes" />
  5.   <artist birth_year="1930" name="Ray Charles" />
  6. </favorite_artists>
As you can see, the firstAlbumTitle attribute is missing for Ray Charles. This is because he does not have any albums in our test database and processing the "value" option without any records just makes no sense.

In the last example I'd like to demonstrate the use of $options['sql_options'] within a complex attribute specification. As stated before, $options['sql_options']['single_record'] is always automatically set to true - no matter what you assign to it. This time, we want a "firstAlbum" attribute that has a value of "TITLE (GENRE)" - remember that "genre" is a colum of the artist table while "title" is a column of the album table.

  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.     'attributes' => array(
  12.       'name',
  13.       'birth_year',
  14.       'firstAlbum' => array(
  15.         'value' => '#combineTitleAndGenre()',
  16.         'sql' => array(
  17.           'data' => array(
  18.             'artistid'
  19.           ),
  20.           'query' => "SELECT * FROM album WHERE artist_id = ? ORDER BY published_year"
  21.         ),
  22.         'sql_options' => array(
  23.           'merge_selective' => array('genre')
  24.         )
  25.       )
  26.     )
  27.   )
  28. );
  29. header('Content-Type: application/xml');
  30. $dom->formatOutput true;
  31. print $dom->saveXML();
  32.  
  33. function combineTitleAndGenre($record)
  34. {
  35.     return $record['title'' (' $record['genre'')';
  36. }
  37. ?>
This results in the following XML data:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist name="Curtis Mayfield" birth_year="1920" firstAlbum="Curtis (Soul)"/>
  4.   <artist name="Isaac Hayes" birth_year="1942" firstAlbum="Shaft (Soul)"/>
  5.   <artist name="Ray Charles" birth_year="1930"/>
  6. </favorite_artists>


Using the Asterisk Shortcut

The asterisk shortcut only works with Simple Attribute Specifications (and Simple Element Specifications).

Everything said about Using the Asterisk Shortcut with simple element specifications applies here to! The simplest example of using the asterisk shortcut with the attributes option is as follows:

  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.     'attributes' => array(
  12.       '*'
  13.     )
  14.   )
  15. );
  16. header('Content-Type: application/xml');
  17. $dom->formatOutput true;
  18. print $dom->saveXML();
  19. ?>
This produces this XML data:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist artistid="1" birth_place="Chicago" birth_year="1920" genre="Soul" name="Curtis Mayfield" />
  4.   <artist artistid="2" birth_place="Tennessee" birth_year="1942" genre="Soul" name="Isaac Hayes" />
  5.   <artist artistid="3" birth_place="Mississippi" birth_year="1930" genre="Country and Soul" name="Ray Charles" />
  6. </favorite_artists>


$options['rowTag']

The name of the tag that encloses each record. The default is 'row'.

Here goes an example of 'rowTag' being used at the root level:

  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.         'genre'
  15.     )
  16.   )
  17. );
  18. header('Content-Type: application/xml');
  19. $dom->formatOutput true;
  20. print $dom->saveXML();
  21. ?>
'rowTag' was set to 'artist' therefore the resulting XML data is:
  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.     <genre>Soul</genre>
  7.   </artist>
  8.   <artist>
  9.     <name>Isaac Hayes</name>
  10.     <birth_year>1942</birth_year>
  11.     <genre>Soul</genre>
  12.   </artist>
  13.   <artist>
  14.     <name>Ray Charles</name>
  15.     <birth_year>1930</birth_year>
  16.     <genre>Country and Soul</genre>
  17.   </artist>
  18. </favorite_artists>

Now let's have a look at a more advanced 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
  7.         *
  8.      FROM
  9.         artist
  10.         LEFT JOIN album ON album.artist_id = artist.artistid",
  11.     array(
  12.         'rootTag' => 'music_library',
  13.         'rowTag' => 'artist',
  14.         'idColumn' => 'artistid',
  15.         'elements' => array(
  16.             'artistid',
  17.             'name',
  18.             'birth_year',
  19.             'birth_place',
  20.             'genre',
  21.             'albums' => array(
  22.                 'rootTag' => 'albums',
  23.                 'rowTag' => 'album',
  24.                 'idColumn' => 'albumid',
  25.                 'elements' => array(
  26.                     'albumid',
  27.                     'title',
  28.                     'published_year',
  29.                     'comment'
  30.                 )
  31.             )
  32.         )
  33.     )
  34. );
  35.  
  36. header('Content-Type: application/xml');
  37. $dom->formatOutput true;
  38. print $dom->saveXML();
  39. ?>
Here 'rowTag' on the root level is set to 'artist' while ['elements']['albums']['rowTag'] is set to 'album'. This example is taken from Case 02: LEFT OUTER JOIN, so please see there for the resulting XML data and further discussion.

In some situations, 'rowTag' can be omitted all together:

  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. ?>
Here the complex element definition ['elements']['name'] has no 'rowTag' option. This is alright because the specification's array key ('name' in this case) is used by default.

$options['dynamicRowTag']

Use this option if you want the name of an XML element determined at run time (e.g. you want to pull the XML element name from the database). Note: if this option is present, $options['rowTag'] will be ignored.

What you can assign to $options['dynamicRowTag'] is very similar as what you can use for $options['value'] or a Simple Element Specification. $options['dynamicRowTag'] can be of the following types:

  • COLUMN NAME: 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: if the value is preceeded by a colon (':'), it is interpreted as static text.
  • CALLBACK FUNCTION with a # prefix: 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 obviously has to be a string that is a valid XML element name.
  • 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.

Let's have a look at a straightforward example: we want our customer's email addresses inside a tag named after the customer's first name, e.g. <John>john.doe@example.com</John>:

  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 customer",
  7.   array(
  8.     'rootTag' => 'customers',
  9.     'idColumn' => 'customerid',
  10.     'rowTag' => 'customer',
  11.     'elements' => array(
  12.         'customerid',
  13.         'name_and_email' => array(
  14.             'dynamicRowTag' => 'first_name',
  15.             'value' => 'email'
  16.         )
  17.     )
  18.   )
  19. );
  20. header('Content-Type: application/xml');
  21. $dom->formatOutput true;
  22. print $dom->saveXML();
  23. ?>
The resulting XML looks like this:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <customers>
  3.   <customer>
  4.     <customerid>1</customerid>
  5.     <Jane>jane.doe@example.com</Jane>
  6.   </customer>
  7.   <customer>
  8.     <customerid>2</customerid>
  9.     <John>john.doe@example.com</John>
  10.   </customer>
  11.   <customer>
  12.     <customerid>3</customerid>
  13.     <Susan>susan.green@example.com</Susan>
  14.   </customer>
  15.   <customer>
  16.     <customerid>4</customerid>
  17.     <Victoria>victory.alt@example.com</Victoria>
  18.   </customer>
  19.   <customer>
  20.     <customerid>5</customerid>
  21.     <Will>will.wippy@example.com</Will>
  22.   </customer>
  23.   <customer>
  24.     <customerid>6</customerid>
  25.     <Tim>tim.raw@example.com</Tim>
  26.   </customer>
  27.   <customer>
  28.     <customerid>7</customerid>
  29.     <Nick>nick.fallow@example.com</Nick>
  30.   </customer>
  31.   <customer>
  32.     <customerid>8</customerid>
  33.     <Ed>ed.burton@example.com</Ed>
  34.   </customer>
  35.   <customer>
  36.     <customerid>9</customerid>
  37.     <Jack>jack.woo@example.com</Jack>
  38.   </customer>
  39.   <customer>
  40.     <customerid>10</customerid>
  41.     <Maria>maria.gonzales@example.com</Maria>
  42.   </customer>
  43. </customers>

$options['rootTag']

The name of the root tag that encloses all other tags. On the root level, the default is 'root'. On all other levels omitting the rootTag option means that the row tags will not be enclosed by a root tag but will directly be placed inside the parent tag.

Here goes an example of 'rootTag' being used at the root level:

  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.         'genre'
  15.     )
  16.   )
  17. );
  18. header('Content-Type: application/xml');
  19. $dom->formatOutput true;
  20. print $dom->saveXML();
  21. ?>
'rootTag' was set to 'favorite_artists'. The resulting XML data therefore is:
  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.   <genre>Soul</genre>
  7. </artist>
  8. <artist>
  9.   <name>Isaac Hayes</name>
  10.   <birth_year>1942</birth_year>
  11.   <genre>Soul</genre>
  12. </artist>
  13. <artist>
  14.   <name>Ray Charles</name>
  15.   <birth_year>1930</birth_year>
  16.   <genre>Country and Soul</genre>
  17. </artist>
  18. </favorite_artists>

Here goes an example with the rootTag being used at a lower level:

  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
  7.         *
  8.      FROM
  9.         artist
  10.         LEFT JOIN album ON album.artist_id = artist.artistid",
  11.     array(
  12.         'rootTag' => 'music_library',
  13.         'rowTag' => 'artist',
  14.         'idColumn' => 'artistid',
  15.         'elements' => array(
  16.             'artistid',
  17.             'name',
  18.             'birth_year',
  19.             'birth_place',
  20.             'genre',
  21.             'albums' => array(
  22.                 'rootTag' => 'albums',
  23.                 'rowTag' => 'album',
  24.                 'idColumn' => 'albumid',
  25.                 'elements' => array(
  26.                     'albumid',
  27.                     'title',
  28.                     'published_year',
  29.                     'comment'
  30.                 )
  31.             )
  32.         )
  33.     )
  34. );
  35.  
  36. header('Content-Type: application/xml');
  37. $dom->formatOutput true;
  38. print $dom->saveXML();
  39. ?>
['elements']['albums']['rootTag'] is set to 'albums'. Therefore all 'album' tags of a single artist will be enclosed by a singel 'albums' tag. This example is actually taken from Case 02: LEFT OUTER JOIN, so please see there for the resulting XML data and further discussion.

As shown in Case 04: Case 03 with custom tag names, attributes, merge_selective and more is is also possible to assign an empty string to the rootTag option or to omit it at all. In our case this results in all the album tags not being surrounded by a single 'albums' tag but being directly placed inside the 'artist' tag:

  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
  7.         *
  8.      FROM
  9.         artist
  10.         LEFT JOIN album ON album.artist_id = artist.artistid",
  11.     array(
  12.         'rootTag' => 'music_library',
  13.         'rowTag' => 'artist',
  14.         'idColumn' => 'artistid',
  15.         'elements' => array(
  16.             'artistid',
  17.             'name',
  18.             'birth_year',
  19.             'birth_place',
  20.             'genre',
  21.             'albums' => array(
  22.                 'rowTag' => 'album',
  23.                 'idColumn' => 'albumid',
  24.                 'elements' => array(
  25.                     'albumid',
  26.                     'title',
  27.                     'published_year',
  28.                     'comment'
  29.                 )
  30.             )
  31.         )
  32.     )
  33. );
  34.  
  35. header('Content-Type: application/xml');
  36. $dom->formatOutput true;
  37. print $dom->saveXML();
  38. ?>
The resulting XML looks like this:
  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.     <album>
  10.       <albumid>1</albumid>
  11.       <title>New World Order</title>
  12.       <published_year>1990</published_year>
  13.       <comment>the best ever!</comment>
  14.     </album>
  15.     <album>
  16.       <albumid>2</albumid>
  17.       <title>Curtis</title>
  18.       <published_year>1970</published_year>
  19.       <comment>that man's got somthin' to say</comment>
  20.     </album>
  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.     <album>
  29.       <albumid>3</albumid>
  30.       <title>Shaft</title>
  31.       <published_year>1972</published_year>
  32.       <comment>he's the man</comment>
  33.     </album>
  34.   </artist>
  35.   <artist>
  36.     <artistid>3</artistid>
  37.     <name>Ray Charles</name>
  38.     <birth_year>1930</birth_year>
  39.     <birth_place>Mississippi</birth_place>
  40.     <genre>Country and Soul</genre>
  41.   </artist>
  42. </music_library>

Note however that a hidden child element is used as a container to ensure the order of the generated XML elements. Internally all elements with a name that starts with '__' are hidden. An explicit definition of the hidden complex element would look like this:
  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
  7.         *
  8.      FROM
  9.         artist
  10.         LEFT JOIN album ON album.artist_id = artist.artistid",
  11.     array(
  12.         'rootTag' => 'music_library',
  13.         'rowTag' => 'artist',
  14.         'idColumn' => 'artistid',
  15.         'elements' => array(
  16.             'artistid',
  17.             'name',
  18.             'birth_year',
  19.             'birth_place',
  20.             'genre',
  21.             'albums' => array(
  22.                 'rootTag' => '__albums',
  23.                 'rowTag' => 'album',
  24.                 'idColumn' => 'albumid',
  25.                 'elements' => array(
  26.                     'albumid',
  27.                     'title',
  28.                     'published_year',
  29.                     'comment'
  30.                 )
  31.             )
  32.         )
  33.     )
  34. );
  35.  
  36. header('Content-Type: application/xml');
  37. $dom->formatOutput true;
  38. print $dom->saveXML();
  39. ?>

$options['value']

The value of an XML element's child text node. The specification can be of the following types:

  • COLUMN NAME: 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: if the value is preceeded by a colon (':'), it is interpreted as static text.
  • CALLBACK FUNCTION with a # prefix: 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 XML UNSERIALIZATION prefix and the CONDITIONAL prefix cannot be combined with a COMMAND OBJECT.

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

The following example demonstrates the usage of some of the types (for a full demonstration of all types see the second example under Simple Element Specifications). The comment element will be skipped if its value == "". Same holds true for the genre element which uses the trim'ed version of the value stored in the genre column. The comment tag has an attribute named type with a static value of "short text". The published_century element gets the century calculated using floor and has the attribute digitCount with a static value of 2.

  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
  7.     *
  8.    FROM
  9.     album al,
  10.     artist ar
  11.    WHERE
  12.     al.artist_id = ar.artistid",
  13.   array(
  14.     'rootTag' => 'albums',
  15.     'idColumn' => 'albumid',
  16.     'rowTag' => 'album',
  17.     'elements' => array(
  18.         'albumid',
  19.         'title',
  20.         'published_year',
  21.         'published_century' => array(
  22.             'value' => "#Utils::getPublishedYearCentury()",
  23.             'attributes' => array(
  24.                 'digitCount' => ':2'
  25.             )
  26.         ),
  27.         'comment' => array(
  28.             'value' => '?comment',
  29.             'attributes' => array(
  30.                 'type' => ':short text'
  31.             )
  32.         ),
  33.         'genre' => array(
  34.             'value' => "?#Utils::trimGenre()"
  35.         )
  36.     )
  37.   )
  38. );
  39. header('Content-Type: application/xml');
  40. $dom->formatOutput true;
  41. print $dom->saveXML();
  42.  
  43. class Utils
  44. {
  45.     function trimGenre($record)
  46.     {
  47.         return trim($record['genre']);
  48.     }
  49.     
  50.     function getPublishedYearCentury($record)
  51.     {
  52.         return floor($record['published_year']/100);
  53.     }
  54. }
  55. ?>
The resulting XML data looks like this:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <albums>
  3.   <album>
  4.     <albumid>1</albumid>
  5.     <title>New World Order</title>
  6.     <published_year>1990</published_year>
  7.     <published_century digitCount="2">19</published_century>
  8.     <comment type="short text">the best ever!</comment>
  9.     <genre>Soul</genre>
  10.   </album>
  11.   <album>
  12.     <albumid>2</albumid>
  13.     <title>Curtis</title>
  14.     <published_year>1970</published_year>
  15.     <published_century digitCount="2">19</published_century>
  16.     <comment type="short text">that man's got somthin' to say</comment>
  17.     <genre>Soul</genre>
  18.   </album>
  19.   <album>
  20.     <albumid>3</albumid>
  21.     <title>Shaft</title>
  22.     <published_year>1972</published_year>
  23.     <published_century digitCount="2">19</published_century>
  24.     <comment type="short text">he's the man</comment>
  25.     <genre>Soul</genre>
  26.   </album>
  27. </albums>

$options['condition']

This option allows you to specify a condition for the element to be included. The string assigned to the condition option can be of the following types:

  • COLUMN NAME: this is the default if not preceeded by ':' or '#'. If the column does not exist, an XML_Query2XML_ConfigException will be thrown. Remember that the string '0' or '' will both evaluate to false which means that the element would be skipped. Note: in most cases you will be much better off changing your WHERE clause than using this type of condition.
  • STATIC TEXT with a : prefix: if the value is preceeded by a colon (':'), it is interpreted as static text. Remember that the string '0' or '' will both evaluate to false which means that the element would be skipped.
  • CALLBACK FUNCTION with a # prefix: 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.
  • 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. 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.
This option provides a similar function as the "?" prefix for column specifications - see Simple Element Specifications, Simple Attribute Specifications and $options['value']. The difference is that $options['condition'] is more powerful: you can call any external function you like to determin whether the element shall be included. Here goes a little example:
  1. <?php
  2. if (isset($_REQUEST['includeCondition'])) {
  3.     $includeCondition ($_REQUEST['includeCondition'== '1');
  4. else {
  5.     $includeCondition false;
  6. }
  7. require_once 'XML/Query2XML.php';
  8. require_once 'MDB2.php';
  9. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  10. $dom =$query2xml->getXML(
  11.     "SELECT
  12.         *
  13.      FROM
  14.         artist
  15.         LEFT JOIN album ON album.artist_id = artist.artistid",
  16.     array(
  17.         'rootTag' => 'music_library',
  18.         'rowTag' => 'artist',
  19.         'idColumn' => 'artistid',
  20.         'elements' => array(
  21.             'artistid',
  22.             'name',
  23.             'birth_year',
  24.             'birth_place',
  25.             'genre',
  26.             'albums' => array(
  27.                 'rootTag' => 'albums',
  28.                 'rowTag' => 'album',
  29.                 'idColumn' => 'albumid',
  30.                 'condition' => '#isSpecialPublishedYear()',
  31.                 'elements' => array(
  32.                     'albumid',
  33.                     'title',
  34.                     'published_year',
  35.                     'comment' => array(
  36.                         'value' => 'comment',
  37.                         'condition' => ':' ($includeCondition '1' '0')
  38.                     )
  39.                 )
  40.             )
  41.         )
  42.     )
  43. );
  44. header('Content-Type: application/xml');
  45. $dom->formatOutput true;
  46. print $dom->saveXML();
  47.  
  48. /**Returns whether $year is 1970 or 1972.
  49. */
  50. function isSpecialPublishedYear($record)
  51. {
  52.     //do some highly complex calculations ...
  53.     return $record['published_year'== 1970 || $record['published_year'== 1972;
  54. }
  55. ?>
The resulting XML data is:
  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>2</albumid>
  12.         <title>Curtis</title>
  13.         <published_year>1970</published_year>
  14.       </album>
  15.     </albums>
  16.   </artist>
  17.   <artist>
  18.     <artistid>2</artistid>
  19.     <name>Isaac Hayes</name>
  20.     <birth_year>1942</birth_year>
  21.     <birth_place>Tennessee</birth_place>
  22.     <genre>Soul</genre>
  23.     <albums>
  24.       <album>
  25.         <albumid>3</albumid>
  26.         <title>Shaft</title>
  27.         <published_year>1972</published_year>
  28.       </album>
  29.     </albums>
  30.   </artist>
  31.   <artist>
  32.     <artistid>3</artistid>
  33.     <name>Ray Charles</name>
  34.     <birth_year>1930</birth_year>
  35.     <birth_place>Mississippi</birth_place>
  36.     <genre>Country and Soul</genre>
  37.     <albums />
  38.   </artist>
  39. </music_library>

Note that (if present) $options['sql'] will get processed *before* evaluating the condition. This allows you to wirte code like the following:

  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
  7.     *
  8.    FROM
  9.     artist",
  10.   array(
  11.     'rootTag' => 'artists',
  12.     'idColumn' => 'artistid',
  13.     'rowTag' => 'artist',
  14.     'elements' => array(
  15.         'artistid',
  16.         'name',
  17.         'albums' => array(
  18.             'idColumn' => 'albumid',
  19.             'sql' => array(
  20.                 'data' => array(
  21.                     'artistid'
  22.                 ),
  23.                 'query' => "SELECT * FROM album WHERE artist_id = ?",
  24.             ),
  25.             'condition' => '#isGT1980()',
  26.             'elements' => array(
  27.                 'title',
  28.                 'published_year'
  29.             )
  30.         )
  31.     )
  32.   )
  33. );
  34. header('Content-Type: application/xml');
  35. $dom->formatOutput true;
  36. print $dom->saveXML();
  37.  
  38. function isGT1980($record)
  39. {
  40.     return $record['published_year'1980;
  41. }
  42. ?>
"published_year" is a column of the table album but as the "sql" option is processed before evaluating the "condition" option everything works just fine:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <artists>
  3.   <artist>
  4.     <artistid>1</artistid>
  5.     <name>Curtis Mayfield</name>
  6.     <albums>
  7.       <title>New World Order</title>
  8.       <published_year>1990</published_year>
  9.     </albums>
  10.   </artist>
  11.   <artist>
  12.     <artistid>2</artistid>
  13.     <name>Isaac Hayes</name>
  14.   </artist>
  15.   <artist>
  16.     <artistid>3</artistid>
  17.     <name>Ray Charles</name>
  18.   </artist>
  19. </artists>

$options['sql']

Note: This option is driver-specific. The following discussion is limited to the database-related drivers.

This and $options['sql_options'] are the only options that can only be present within Complex Element Specifications. If given at the root level, it would be just ignored. $options['sql'] allows you to split up one huge JOIN into multiple smaller queries. You might want (or have) to do this in several scenarios:

  • Your RDBMS has a maximum number of fields it can return in a single query and you've reached it.
  • You are short on memory: let's say your big JOIN returns 100 fields and you have 10 000 records. It might turn out that the memory consumption is lower if you split up the single big JOIN into multiple quieres that have smaller result sets. As all the data won't be in memory at once, it might even run faster.
  • You are too lazy to think about how to best join these 8 tables :)
You will definitively want to do some Profiling and Performance Tuning before deciding whether or not to split up one big JOIN into multiple smaller JOINs.

There are two ways of specifying $options['sql']:

  • Simple Query Specification: uses the query() method provided by the database abstraction layer (PDO/MDB2/DB/ADOdb) - use it with care
  • Complex Query Specification: uses the prepare() and execute() methods provided by the database abstraction layer and can therefore prevent SQL injection and is also faster in most scenarios

Simple Query Specification

Since v0.8.0 a simple query specifications are purely static strings (in most cases you will want to use a Complex Query Specification):

  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
  7.         *
  8.      FROM
  9.         artist
  10.      WHERE
  11.         artistid = 1",
  12.     array(
  13.         'rootTag' => 'music_library',
  14.         'rowTag' => 'artist',
  15.         'idColumn' => 'artistid',
  16.         'elements' => array(
  17.             'artistid',
  18.             'name',
  19.             'birth_year',
  20.             'birth_place',
  21.             'genre',
  22.             'albums' => array(
  23.                 'sql' => 'SELECT * FROM album WHERE artist_id = 1',
  24.                 'rootTag' => 'albums',
  25.                 'rowTag' => 'album',
  26.                 'idColumn' => 'albumid',
  27.                 'elements' => array(
  28.                     'albumid',
  29.                     'title',
  30.                     'published_year',
  31.                     'comment'
  32.                 )
  33.             )
  34.         )
  35.     )
  36. );
  37.  
  38. header('Content-Type: application/xml');
  39. $dom->formatOutput true;
  40. print $dom->saveXML();
  41. ?>

To understand how $options['sql'] really works, some knowledge of XML_Query2XML's internals might be helpful: XML_Query2XML::getXML() calls the private method XML_Query2XML::_getNestedXMLRecord() for every record retrieved from the database using the SQL statement passed to getXML() as first argument. XML_Query2XML::_getNestedXMLRecord() will then process the current record according to the settings specified in $options. The processing of all Complex Element Specifications is handed off to the private method XML_Query2XML::_processComplexElementSpecification(). XML_Query2XML::_processComplexElementSpecification() in turn will call the private method XML_Query2XML::_applySqlOptionsToRecord() to interpret $options['sql'] and $options['sql_options']. XML_Query2XML::_processComplexElementSpecification() will then call again XML_Query2XML::_getNestedXMLRecord() for every record retrieved using the query specified in the 'sql' option.


Complex Query Specification

A Complex Query Specification uses the database abstraction layer's prepare() and execute() methods and therefore prevents SQL injection and is also faster than a Simple Query Specification in most scenarios. It can consist of multiple parts (only $options['sql']['query'] is mandatory):

  • $options['sql']['query']: the SQL query as a string that contains a placeholder for each element of $options['sql']['data'].
  • $options['sql']['driver']: allows you to use a different XML_Query2XML_Driver for this complex query than the one passed to XML_Query2XML::factory(). Please see Using Multiple Drivers for details. $options['sql']['driver'] is optional.
  • $options['sql']['limit']: allows you to limit the number of records returned from the query. It has to be a numeric value. Please note that a value of 0 (or '0') is equivalent to not setting $options['sql']['limit'] at all. $options['sql']['limit'] and $options['sql']['offset'] are only interpreted by the drivers for PEAR MDB2 and PEAR DB. All other drivers simply ignore these two options.
  • $options['sql']['offset']: allows you to set the number of the first record to retrieve. This has to be a numeric value. The default is 0. Please note that this option will be ignored unless $options['sql']['limit'] is set. $options['sql']['offset'] and $options['sql']['limit'] are only interpreted by the drivers for PEAR MDB2 and PEAR DB. All other drivers simply ignore these two options.
  • $options['sql']['data']: an indexed array of values. This is optional. The specification can be of the following types:
    • COLUMN NAME: this is the default if not preceeded by ':' or '#'. If the column does not exist, an XML_Query2XML_ConfigException will be thrown. Note that the parent record will be used! This is quite logic as this SQL statement has not been executed yet :)
    • STATIC TEXT with a : prefix: if the value is preceeded by a colon (':'), it is interpreted as static text.
    • CALLBACK FUNCTION with a # prefix: 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.
    • 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. 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.
    The same syntax (with the additional '?' prefix) can be use for Simple Element Specifications, $options['value'], Simple Attribute Specifications and $options['idColumn'] because the private method XML_Query2XML::_applyColumnStringToRecord() is used in all cases. Note: $options['sql']['data'] is optional!
Here is a simple example similar to Case 03: Two SELECTs instead of a LEFT OUTER JOIN:

  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
  7.         *
  8.      FROM
  9.         artist",
  10.     array(
  11.         'rootTag' => 'music_library',
  12.         'rowTag' => 'artist',
  13.         'idColumn' => 'artistid',
  14.         'elements' => array(
  15.             'artistid',
  16.             'name',
  17.             'birth_year',
  18.             'birth_place',
  19.             'genre',
  20.             'albums' => array(
  21.                 'sql' => array(
  22.                     'data' => array(
  23.                         'artistid'
  24.                     ),
  25.                     'query' => "SELECT * FROM album WHERE artist_id = ?"
  26.                 ),
  27.                 'rootTag' => 'albums',
  28.                 'rowTag' => 'album',
  29.                 'idColumn' => 'albumid',
  30.                 'elements' => array(
  31.                     'albumid',
  32.                     'title',
  33.                     'published_year',
  34.                     'comment'
  35.                 )
  36.             )
  37.         )
  38.     )
  39. );
  40.  
  41. header('Content-Type: application/xml');
  42. $dom->formatOutput true;
  43. print $dom->saveXML();
  44. ?>


$options['sql_options']

This allows you to specify how $options['sql'] is handled. $options['sql_options'] is an associative array that can have the following fileds:

Per default all options are set to the boolean value false.

$options['sql_options']['cached']

Since 1.5.0RC1 Caching is deactivated by default. If caching is activated the result of a query is stored in the private associative array XML_Query2XML::$_recordCache using the SQL query string as key. If the exact same query needs to be executed a second time, its results can be retrieved from cache.

Before setting $options['sql_options']['cached'] to true, do some Profiling and Performance Tuning. As documented in XML_Query2XML::getProfile() the CACHED column in the profile output will show 'true!' if caching is performed without being necessary.

Caching only makes sense, if you have to run exactly the same query multiple times.


$options['sql_options']['single_record']

Use this option to make sure that the SQL query you specified in $options['sql'] returns only a single record. This option is in fact of limited use. Do not use it to fetch only the first record from a large result set. (SQL is your friend: use a better WHERE clause!)


$options['sql_options']['merge']

By default no merging is done so that less memory is used. This means that the data of the record present on the parent level will not be available at this level. Only the data returned by $options['sql'] will be available (and therefore use up memory). If you also need the data of the record present on the parent level the two arrays have to be merged using array_merge(). If $options['sql'] returned multiple records, each of them has to be merged with the one of the parent level separatly:

  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
  7.         *
  8.      FROM
  9.         artist",
  10.     array(
  11.         'rootTag' => 'MUSIC_LIBRARY',
  12.         'rowTag' => 'ARTIST',
  13.         'idColumn' => 'artistid',
  14.         'elements' => array(
  15.             'NAME' => 'name',
  16.             'BIRTH_YEAR' => 'birth_year',
  17.             'GENRE' => 'genre',
  18.             'albums' => array(
  19.                 'sql' => array(
  20.                     'data' => array('artistid'),
  21.                     'query' => "SELECT * FROM album WHERE artist_id = ?"
  22.                 ),
  23.                 'sql_options' => array(
  24.                     'merge' => true
  25.                 ),
  26.                 'rootTag' => '',
  27.                 'rowTag' => 'ALBUM',
  28.                 'idColumn' => 'albumid',
  29.                 'elements' => array(
  30.                     'TITLE' => 'title',
  31.                     'PUBLISHED_YEAR' => 'published_year',
  32.                     'COMMENT' => 'comment',
  33.                     'GENRE' => 'genre'
  34.                 )
  35.             )
  36.         )
  37.     )
  38. );
  39.  
  40. header('Content-Type: application/xml');
  41. $dom->formatOutput true;
  42. print $dom->saveXML();
  43. ?>
This produces quite some overhead. It is therefore highly recommended to use $options['sql_options']['merge_selective'] described in the next section.


$options['sql_options']['merge_selective']

As a full merge with the parent record might severly affect the performance, the sql option merge_selective allows you to only merge the current record with specific columns of the parent record. Just place the names of all columns of the parent record you want to be available in the current record in an array and assign it to the merge_selective option. Here goes an 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
  7.         *
  8.      FROM
  9.         artist",
  10.     array(
  11.         'rootTag' => 'MUSIC_LIBRARY',
  12.         'rowTag' => 'ARTIST',
  13.         'idColumn' => 'artistid',
  14.         'elements' => array(
  15.             'NAME' => 'name',
  16.             'BIRTH_YEAR' => 'birth_year',
  17.             'GENRE' => 'genre',
  18.             'albums' => array(
  19.                 'sql' => array(
  20.                     'data' => array('artistid'),
  21.                     'query' => "SELECT * FROM album WHERE artist_id = ?"
  22.                 ),
  23.                 'sql_options' => array(
  24.                     'merge_selective' => array('genre')
  25.                 ),
  26.                 'rootTag' => '',
  27.                 'rowTag' => 'ALBUM',
  28.                 'idColumn' => 'albumid',
  29.                 'elements' => array(
  30.                     'TITLE' => 'title',
  31.                     'PUBLISHED_YEAR' => 'published_year',
  32.                     'COMMENT' => 'comment',
  33.                     'GENRE' => 'genre'
  34.                 )
  35.             )
  36.         )
  37.     )
  38. );
  39.  
  40. header('Content-Type: application/xml');
  41. $dom->formatOutput true;
  42. print $dom->saveXML();
  43. ?>
Please see Case 04: Case 03 with custom tag names, attributes, merge_selective and more for a similar example and more discussion of $options['sql_options']['merge_selective'].


$options['sql_options']['merge_master']

If (selective) merging is performed, it might become important which record overwrites the data of the other. As soon as both result sets have a column with the same name, there is a confilict that has to be resolved. By default, the record of the parent level is the master and overwrites the record(s) returned by $options['sql']. If you want the new records to overwrite the record of the parent level, set $options['sql_options']['merge_master'] to true. Note that this option only has an effect if $options['sql_options']['merge'] is set to true or $options['sql_options']['merge_selective'] is used.


$options['mapper']

This option allows you to specifiy a function for mapping SQL identifiers to XML names. Whenever you use a Simple Element Specification or a Simple Attribute Specification only with a column name and without a tag/attribute name, the specified column name will be used for the tag/attribute name. Please note that mapping is also performed when the Using the Asterisk Shortcut is used. Per default $options['mapper'] is set to false which means that no special mapping is used. $options['mapper'] can have one of the following formats:

  • 'CLASS::STATIC_METHOD': this syntax allows you to use a static method for mapping:
    1. 'mapper' => 'MyMapper::map'
  • array('CLASS', 'STATIC_METHOD'): this syntax also allows you to use a static method for mapping:
    1. 'mapper' => array('MyMapper''map')
  • array($instance, 'METHOD'): this syntax allows you to use a non-static method for mapping:
    1. 'mapper' => array($myMap'map')
  • 'FUNCTION': this syntax allows you to use a regular function for mapping:
    1. 'mapper' => 'myUppercaseMapper'
  • false: use the boolean value false (or any other value that == false) to deactivate any special mapping:
    1. 'mapper' => false
Remember that the mapping only applies to Simple Element Specifications and Simple Attribute Specifications that do not explicitly have a tag/attribute name or those that have a tag/attribute name that contains an asterisk shortcut. The following example will also show that a mapper defined at the root level is also used at all lower levels (unless it gets overwritten, see Using multiple mappers):
  1. <?php
  2. class SomeMapper
  3. {
  4.     public function map($str)
  5.     {
  6.         //do something with $str
  7.         return $str;
  8.     }
  9. }
  10.  
  11. require_once 'XML/Query2XML.php';
  12. require_once 'XML/Query2XML/ISO9075Mapper.php';
  13. require_once 'MDB2.php';
  14. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  15. $dom $query2xml->getXML(           //
  16.   "SELECT * FROM artist",            //
  17.   array(                             //
  18.     'rootTag' => 'favorite_artists'//no mapping
  19.     'idColumn' => 'artistid',        //nothing to map
  20.     'rowTag' => 'artist',            //no mapping
  21.     'mapper' => 'SomeMapper::map',   //
  22.     'elements' => array(             //
  23.       'artistid',                 //mapping
  24.       'NAME' => 'name',           //no mapping as the tag name is specified
  25.       '*',                        //mapping
  26.       'TAG_*' => '*',             //does a mapping too!
  27.       'albums' => array(          //nothing to map
  28.         'sql' => array(           //
  29.           'data' => array(        //
  30.             'artistid'            //nothing to map
  31.           ),                      //
  32.           'query' => 'SELECT * FROM album WHERE artist_id = ?'      //
  33.         ),                      //
  34.         'rootTag' => 'albums',  //no mapping
  35.         'rowTag' => 'album',    //no mapping
  36.         'idColumn' => 'albumid',//nothing to map
  37.         'elements' => array(    //
  38.           'albumid',          //mapping using the mapper specified at the root level
  39.           'title',            //mapping using the mapper specified at the root level
  40.           'published_year',   //mapping using the mapper specified at the root level
  41.           'comment'           //mapping using the mapper specified at the root level
  42.         )                     //
  43.       )                       //
  44.     ),                        //
  45.     'attributes' => array(        //
  46.       'artistid',                 //mapping
  47.       'NAME' => 'name',           //no mapping as the tag name is specified
  48.       '*',                        //mapping
  49.       'TAG_*' => '*'              //does a mapping too!
  50.     )                             //
  51.   )                               //
  52. );                                //
  53. header('Content-Type: application/xml');    //
  54. print $dom->saveXML();                      //
  55. ?>

Mapping SQL identifiers to XML names in accordance with ISO/IEC 9075-14:2005

The package XML_Query2XML also implements the Final Committee Draft for ISO/IEC 9075-14:2005, section "9.1 Mapping SQL <identifier>s to XML Names". ISO/IEC 9075-14:2005 is available online at http://www.sqlx.org/SQL-XML-documents/5FCD-14-XML-2004-07.pdf.

A lot of characters are legal in SQL identifiers but cannot be used within XML names. To begin with, SQL identifiers can contain any Unicode character while XML names are limited to a certain set of characters. E.g the SQL identifier "<21yrs in age" obviously is not a valid XML name. '#', '{', and '}' are also not allowed. Fully escaped SQL identifiers also must not contain a column (':') or start with "xml" (in any case combination). Illegal characters are mapped to a string of the form _xUUUU_ where UUUU is the Unicode value of the character.

The following is a table of example mappings:

+----------------+------------------------+------------------------------------+
| SQL-Identifier | Fully escaped XML name | Comment                            |
+----------------+------------------------+------------------------------------+
| dept:id        | dept_x003A_id          | ":" is illegal                     |
| xml_name       | _x0078_ml_name         | must not start with [Xx][Mm][Ll]   |
| XML_name       | _x0058_ML_name         | must not start with [Xx][Mm][Ll]   |
| hire date      | hire_x0020_date        | space is illegal too               |
| Works@home     | Works_x0040_home       | "@" is illegal                     |
| file_xls       | file_x005F_xls         | "_" gets mapped if followed by "x" |
| FIRST_NAME     | FIRST_NAME             | no problem here                    |
+----------------+------------------------+------------------------------------+
     

The ISO 9075-mapping does produce some overhead which might not be needed in a lot of situations. Therefore it is not the default mapper. In most cases it will be sufficient to validate your XML schema once using tools like the free XMLSpy Home Edition.

To use the ISO 9075-mapper that comes with XML_Query2XML you have to:

Here goes an example:
  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'XML/Query2XML/ISO9075Mapper.php';
  4. require_once 'MDB2.php';
  5. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  6. $dom $query2xml->getXML(
  7.   "SELECT * FROM artist",
  8.   array(
  9.     'rootTag' => 'favorite_artists',
  10.     'idColumn' => 'artistid',
  11.     'rowTag' => 'artist',
  12.     'mapper' => 'XML_Query2XML_ISO9075Mapper::map',
  13.     'elements' => array(
  14.         '*'
  15.     )
  16.   )
  17. );
  18. header('Content-Type: application/xml');
  19. $dom->formatOutput true;
  20. print $dom->saveXML();
  21. ?>


Building your own mappers

There are cases when you will want the tag and attribute names to be somehow different from the column names. Let's say you want to use the column names as tag and attribute names but make them all uppercase. Certainly you could write code like this:

  1. <?php
  2. require_once 'XML/Query2XML.php';
  3. require_once 'XML/Query2XML/ISO9075Mapper.php';
  4. require_once 'MDB2.php';
  5. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  6. $dom $query2xml->getXML(
  7.   "SELECT * FROM artist",
  8.   array(
  9.     'rootTag' => 'favorite_artists',
  10.     'idColumn' => 'artistid',
  11.     'rowTag' => 'artist',
  12.     'elements' => array(
  13.         'NAME' => 'name',
  14.         'BIRTH_YEAR' => 'birth_year',
  15.         'BIRTH_PLACE' => 'birth_place',
  16.         'GENRE' => 'genre',
  17.     ),
  18.     'attributes' => array(
  19.         'ARTISTID' => 'artistid'
  20.     )
  21.   )
  22. );
  23. header('Content-Type: application/xml');
  24. $dom->formatOutput true;
  25. print $dom->saveXML();
  26. ?>
But that seems a little redundant, doesn't it? In cases like these it is recommended to write your own mapper. As we want to write OO code we don't implement our mapper as a function but as a static public method of the new class UppercaseMapper. The mapper must take a string as an argument and must return a string:
  1. <?php
  2. class UppercaseMapper
  3. {
  4.     public function map($str)
  5.     {
  6.         return strtoupper($str);
  7.     }
  8. }
  9.  
  10. require_once 'XML/Query2XML.php';
  11. require_once 'MDB2.php';
  12. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  13. $dom $query2xml->getXML(
  14.   "SELECT * FROM artist",
  15.   array(
  16.     'rootTag' => 'favorite_artists',
  17.     'idColumn' => 'artistid',
  18.     'rowTag' => 'artist',
  19.     'mapper' => 'UppercaseMapper::map',
  20.     'elements' => array(
  21.         'name',
  22.         'birth_year',
  23.         'birth_place',
  24.         'genre',
  25.     ),
  26.     'attributes' => array(
  27.         'artistid'
  28.     )
  29.   )
  30. );
  31. header('Content-Type: application/xml');
  32. $dom->formatOutput true;
  33. print $dom->saveXML();
  34. ?>
The resulting XML data looks like this:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <favorite_artists>
  3.   <artist ARTISTID="1">
  4.     <NAME>Curtis Mayfield</NAME>
  5.     <BIRTH_YEAR>1920</BIRTH_YEAR>
  6.     <BIRTH_PLACE>Chicago</BIRTH_PLACE>
  7.     <GENRE>Soul</GENRE>
  8.   </artist>
  9.   <artist ARTISTID="2">
  10.     <NAME>Isaac Hayes</NAME>
  11.     <BIRTH_YEAR>1942</BIRTH_YEAR>
  12.     <BIRTH_PLACE>Tennessee</BIRTH_PLACE>
  13.     <GENRE>Soul</GENRE>
  14.   </artist>
  15.   <artist ARTISTID="3">
  16.     <NAME>Ray Charles</NAME>
  17.     <BIRTH_YEAR>1930</BIRTH_YEAR>
  18.     <BIRTH_PLACE>Mississippi</BIRTH_PLACE>
  19.     <GENRE>Country and Soul</GENRE>
  20.   </artist>
  21. </favorite_artists>


Using multiple mappers

Let's say we want to force all tags corresponding to columns of the artist table to be uppercase and all tags corresponding to columns of the album table to be lowercase. This can be done using two mappers:

  1. <?php
  2. class MyMappers
  3. {
  4.     public function uppercaseMapper($str)
  5.     {
  6.         return strtoupper($str);
  7.     }
  8.     
  9.     public function lowercaseMapper($str)
  10.     {
  11.         return strtolower($str);
  12.     }
  13. }
  14.  
  15. require_once 'XML/Query2XML.php';
  16. require_once 'MDB2.php';
  17. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  18. $dom $query2xml->getXML(
  19.     "SELECT
  20.         *
  21.      FROM
  22.         artist",
  23.     array(
  24.         'rootTag' => 'music_library',
  25.         'rowTag' => 'artist',
  26.         'idColumn' => 'artistid',
  27.         'mapper' => 'MyMappers::uppercaseMapper',
  28.         'elements' => array(
  29.             '*',
  30.             'albums' => array(
  31.                 'sql' => array(
  32.                     'data' => array(
  33.                         'artistid'
  34.                     ),
  35.                     'query' => 'SELECT * FROM album WHERE artist_id = ?'
  36.                 ),
  37.                 'rootTag' => 'albums',
  38.                 'rowTag' => 'album',
  39.                 'idColumn' => 'albumid',
  40.                 'mapper' => 'MyMappers::lowercaseMapper',
  41.                 'elements' => array(
  42.                     '*',
  43.                     'artist_id' => '?:'
  44.                 )
  45.             )
  46.         )
  47.     )
  48. );
  49. header('Content-Type: application/xml');
  50. $dom->formatOutput true;
  51. print $dom->saveXML();
  52. ?>
As we know that the columns of the album table already are lowercase we could as well use one mapper and just deactivate that for the complex element "albums':
  1. <?php
  2. class MyMappers
  3. {
  4.     public function uppercaseMapper($str)
  5.     {
  6.         return strtoupper($str);
  7.     }
  8. }
  9.  
  10. require_once 'XML/Query2XML.php';
  11. require_once 'MDB2.php';
  12. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  13. $dom $query2xml->getXML(
  14.     "SELECT
  15.         *
  16.      FROM
  17.         artist",
  18.     array(
  19.         'rootTag' => 'music_library',
  20.         'rowTag' => 'artist',
  21.         'idColumn' => 'artistid',
  22.         'mapper' => 'MyMappers::uppercaseMapper',
  23.         'elements' => array(
  24.             '*',
  25.             'albums' => array(
  26.                 'sql' => array(
  27.                     'data' => array(
  28.                         'artistid'
  29.                     ),
  30.                     'query' => 'SELECT * FROM album WHERE artist_id = ?'
  31.                 ),
  32.                 'rootTag' => 'albums',
  33.                 'rowTag' => 'album',
  34.                 'idColumn' => 'albumid',
  35.                 'mapper' => false,
  36.                 'elements' => array(
  37.                     '*',
  38.                     'artist_id' => '?:'
  39.                 )
  40.             )
  41.         )
  42.     )
  43. );
  44. header('Content-Type: application/xml');
  45. $dom->formatOutput true;
  46. print $dom->saveXML();
  47. ?>
In both cases the resulting XML data will look like this:
  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.         <comment>the best ever!</comment>
  15.       </album>
  16.       <album>
  17.         <albumid>2</albumid>
  18.         <title>Curtis</title>
  19.         <published_year>1970</published_year>
  20.         <comment>that man's got somthin' to say</comment>
  21.       </album>
  22.     </albums>
  23.   </artist>
  24.   <artist>
  25.     <ARTISTID>2</ARTISTID>
  26.     <NAME>Isaac Hayes</NAME>
  27.     <BIRTH_YEAR>1942</BIRTH_YEAR>
  28.     <BIRTH_PLACE>Tennessee</BIRTH_PLACE>
  29.     <GENRE>Soul</GENRE>
  30.     <albums>
  31.       <album>
  32.         <albumid>3</albumid>
  33.         <title>Shaft</title>
  34.         <published_year>1972</published_year>
  35.         <comment>he's the man</comment>
  36.       </album>
  37.     </albums>
  38.   </artist>
  39.   <artist>
  40.     <ARTISTID>3</ARTISTID>
  41.     <NAME>Ray Charles</NAME>
  42.     <BIRTH_YEAR>1930</BIRTH_YEAR>
  43.     <BIRTH_PLACE>Mississippi</BIRTH_PLACE>
  44.     <GENRE>Country and Soul</GENRE>
  45.     <albums />
  46.   </artist>
  47. </music_library>


$options['encoder']

This option allows you to specifiy a function/method that performs the XML encoding for node and attribute values. Per default it is assumed that all data is in ISO-8859-1 (Latin-1) and will be encoded to UTF-8 using mb_convert_encoding() or if not available using utf8_encode().

For some introduction to XML encoding please see http://www.w3schools.com/xml/xml_encoding.asp and http://www.opentag.com/xfaq_enc.htm. Note: I highly recommend to use UTF-8 for XML if you don't have a compelling reason to use an other encoding standard.

The default encoding mechanism (ISO-8859-1 to UTF-8) will be just fine in most cases but sometimes your data might already be in in UTF-8 or you might not want your XML to be UTF-8 encoded at all.

Please see XML encoding for how to change the encoding standard used in the XML declaration.

$options['encoder'] can have one of the following formats:

  • 'CLASS::STATIC_METHOD': this syntax allows you to use a static method for encoding:
    1. 'encoder' => 'MyEncoder::encode'
  • array('CLASS', 'STATIC_METHOD'): this syntax also allows you to use a static method for encoding:
    1. 'encoder' => array('MyEncoder''encode')
  • array($instance, 'METHOD'): this syntax allows you to use a non-static method for encoding:
    1. 'encoder' => array($myEncoder'encode')
  • 'FUNCTION': this syntax allows you to use a regular function for encoding:
    1. 'encoder' => 'myUTF8toISO88591Encoder'
  • false: use the boolean value false to deactivate encoding:
    1. 'encoder' => false
  • null: use NULL to reset encoding to the built-in default encoding. This default assumes that all data is in ISO-8859-1 (Latin-1) and will encode it to UTF-8 using mb_convert_encoding() or if not available using utf8_encode().
    1. 'encoder' => null
One thing you should keep in mind when writing your own encoding (wrapper) functions is that the encoder will only be called if the current record has a string value for that column; i.e. the encoder will not be called if the column value is NULL.

The following example will show that an encoder defined at the root level is also used at all lower levels (unless it gets overwritten, see Using multiple encoders):

  1. <?php
  2. class SomeEncoder
  3. {
  4.     public function encode($str)
  5.     {
  6.         //do something with $str
  7.         return $str;
  8.     }
  9. }
  10.  
  11. require_once 'XML/Query2XML.php';
  12. require_once 'XML/Query2XML/ISO9075Mapper.php';
  13. require_once 'MDB2.php';
  14. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  15. $dom $query2xml->getXML(
  16.   "SELECT * FROM artist",
  17.   array(
  18.     'rootTag' => 'favorite_artists',
  19.     'idColumn' => 'artistid',
  20.     'rowTag' => 'artist',
  21.     'encoder' => 'SomeEncoder::encode',     /* we define an encoder at the root level */
  22.     'elements' => array(
  23.       'artistid',                           // encoding will be
  24.       'name',                               // performed on these
  25.       'albums' => array(
  26.         'sql' => array(
  27.           'data' => array(
  28.             'artistid'
  29.           ),
  30.           'query' => 'SELECT * FROM album WHERE artist_id = ?'
  31.         ),
  32.         'rootTag' => 'albums',
  33.         'rowTag' => 'album',
  34.         'idColumn' => 'albumid',
  35.         'elements' => array(
  36.           'albumid',            // encoder setting is affective on all lower
  37.           'title'               // levels
  38.         ),
  39.         'attributes' => array(
  40.           'comment'             // note: encoding is also performed for attributes
  41.         )
  42.       )
  43.     )
  44.   )
  45. );
  46. header('Content-Type: application/xml');
  47. $dom->formatOutput true;
  48. print $dom->saveXML();
  49. ?>

ISO-8859-1 to UTF-8 encoding (default)

This is what will automatically be performed if you do not use $options['encoder'] at all. This is because most databases use ISO-8859-1 (aka Latin-1) by default. As previously stated, XML_Query2XML will use mb_convert_encoding() or if that is not available utf8_encode() for the actual encoding.

If you have set $options['encoder'] on the root level but wish to switch back to the default on a lower level all you have to do is to use the NULL value:

  1. 'encoder' => null


UTF-8 to ISO-8859-1 encoding

If your data is in UTF-8 but you would like your XML to be in ISO-8859-1 (Latin-1), you can use utf8_decode():

  1. 'encoder' => 'utf8_decode'
or define a wrapper for mb_convert_encoding() and use that:
  1. function utf8ToLatin1($str)
  2. {
  3.     //hint: mb_convert_encoding (str, to_encoding, from_encoding)
  4.     return mb_convert_encoding($str'iso-8859-1''UTF-8');
  5. }
specified as encoder:
  1. 'encoder' => 'utf8ToLatin1'


Disabling encoding

If you data already is in the character set you wish to use for the XML, all you have to do is to disable the encoding by using a boolean value of false:

  1. 'encoder' => false


Using multiple encoders

It might happen to you that some of your data sources are in one character set while others are in another. This means that you need different encoding procedures to convert them all to the same character set you wish to use for the XML (usually UTF-8).

In the first example we will assume that all columns of the table artist are in ISO-8859-1 (Latin-1) while all columns of the table album are in UTF-8.

  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
  7.         *
  8.      FROM
  9.         artist
  10.         LEFT JOIN album ON album.artist_id = artist.artistid",
  11.     array(
  12.         'rootTag' => 'music_library',
  13.         'rowTag' => 'artist',
  14.         'idColumn' => 'artistid',
  15.         'elements' => array(    // all columns of the table artist are in
  16.             'artistid',         // ISO-8859-1; the default conversion therefore
  17.             'name',             // is just fine
  18.             'birth_year',
  19.             'birth_place',
  20.             'genre',
  21.             'albums' => array(
  22.                 'rootTag' => 'albums',
  23.                 'rowTag' => 'album',
  24.                 'idColumn' => 'albumid',
  25.                 'encoder' => false,     // the columns of the album table already are in UTF-8;
  26.                 'elements' => array(    // we therefore have to disable encoding
  27.                     'albumid',
  28.                     'title',
  29.                     'published_year',
  30.                     'comment'
  31.                 )
  32.             )
  33.         )
  34.     )
  35. );
  36.  
  37. header('Content-Type: application/xml');
  38.  
  39. $dom->formatOutput true;
  40. print $dom->saveXML();
  41. ?>

For our second example, let's assume that the following columns use the following character sets:

+----------------------------------------+
| Column         | Character Set         |
+----------------------------------------+
| artist.name    | ISO-8859-1 (Latin-1)  |
| artist.genre   | UTF-8                 |
| album.title    | UTF-16                |
| album.comment  | Windows-1252          |
+----------------+-----------------------+
      
As our XML output shall be in UTF-8 we have to use multiple encoders on a per-column basis:
  1. <?php
  2. function latin1ToUTF8($str)
  3. {
  4.     return utf8_decode($str);
  5.     // alternatively we could have used
  6.     // return mb_convert_encoding($str, 'UTF-8', 'iso-8859-1');
  7. }
  8.  
  9. function utf16ToUTF8($str)
  10. {
  11.     return mb_convert_encoding($str'UTF-8''UTF-16');
  12. }
  13.  
  14. function windows1252ToUTF8($str)
  15. {
  16.     return mb_convert_encoding($str'UTF-8''windows-1252');
  17. }
  18.  
  19. require_once 'XML/Query2XML.php';
  20. require_once 'MDB2.php';
  21. $query2xml XML_Query2XML::factory(MDB2::factory('mysql://root@localhost/Query2XML_Tests'));
  22. $dom $query2xml->getXML(
  23.     "SELECT
  24.         *
  25.      FROM
  26.         artist
  27.         LEFT JOIN album ON album.artist_id = artist.artistid",
  28.     array(
  29.         'rootTag' => 'music_library',
  30.         'rowTag' => 'artist',
  31.         'idColumn' => 'artistid',
  32.         'elements' => array(
  33.             'artistid',
  34.             'name'// name is in ISO-8859-1 and therefore will be handled by the default conversion
  35.             'birth_year',
  36.             'birth_place',
  37.             'genre' => array(
  38.                 'value' => 'genre',
  39.                 'encoder' => false  // genre already is in UTF-8
  40.             ),
  41.             'albums' => array(
  42.                 'rootTag' => 'albums',
  43.                 'rowTag' => 'album',
  44.                 'idColumn' => 'albumid',
  45.                 'elements' => array(
  46.                     'albumid',
  47.                     'title' => array(
  48.                         'value' => 'title',
  49.                         'encoder' => 'utf16ToUTF8'  // title is in UTF-16 and therefore needs
  50.                     ),                              // special treatment
  51.                     'published_year'
  52.                 ),
  53.                 'attributes' => array(
  54.                     'comment' => array(
  55.                         'value' => 'comment',
  56.                         'encoder' => 'windows1252ToUTF8'    // comment is in Windows-1252
  57.                     )
  58.                 )
  59.             )
  60.         )
  61.     )
  62. );
  63.  
  64. header('Content-Type: application/xml');
  65.  
  66. $dom->formatOutput true;
  67. print $dom->saveXML();
  68. ?>


Modifying the returned DOMDocument instance

XML_Query2XML::getXML() returns an instance of DOMDocument. I recommend that you do some reading about PHP5's DOM extension.

Let's see how we can add attributes to the root element (i.e. the first child of the DOMDocument instance returned by getXML()):

  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.  
  6. $doc $query2xml->getXML(
  7.     "SELECT
  8.         *
  9.      FROM
  10.         artist",
  11.     array(
  12.         'rootTag' => 'music_library',
  13.         'rowTag' => 'artist',
  14.         'idColumn' => 'artistid',
  15.         'elements' => array(
  16.             'artistid',
  17.             'name',
  18.             'birth_year',
  19.             'birth_place',
  20.             'genre'
  21.         )
  22.     )
  23. );
  24. $root $doc->firstChild;
  25. $root->setAttribute('copyright''John Doe 2007');
  26.  
  27. header('Content-Type: application/xml');
  28. $doc->formatOutput true;
  29. print $doc->saveXML();
  30. ?>

This adds an attribute named 'copyright' with a value of 'John Doe 2007' to the root element <music_library>:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <music_library copyright="John Doe 2007">
  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.   </artist>
  10.   <artist>
  11.     <artistid>2</artistid>
  12.     <name>Isaac Hayes</name>
  13.     <birth_year>1942</birth_year>
  14.     <birth_place>Tennessee</birth_place>
    <