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:
<?php
require_once 'XML/Query2XML.php';
$pdo = new PDO('mysql://root@localhost/Query2XML_Tests');
?>
Using MDB2 with XML_Query2XML works like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$mdb2 = MDB2::factory('mysql://root@localhost/Query2XML_Tests');
?>
The same thing with PEAR DB looks like that:
<?php
require_once 'XML/Query2XML.php';
require_once 'DB.php';
$db = DB::connect('mysql://root@localhost/Query2XML_Tests');
?>
And again the same thing with ADOdb:
<?php
require_once 'XML/Query2XML.php';
require_once 'adodb/adodb.inc.php';
//require_once 'adodb/adodb-exceptions.inc.php';
//require_once 'adodb/adodb-pear.inc.php';
$adodb = ADONewConnection('mysql');
$adodb->Connect('localhost', 'root', '', 'Query2XML_Tests');
?>
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:
<?php
require_once 'XML/Query2XML.php';
$ldap = Net_LDAP::connect(
'host' => 'ldap.example.com',
'port' => 389,
'version' => 3,
'starttls' => true,
'binddn' => 'cn=Manager,ou=people,dc=example,dc=com',
'bindpw' => 'secret'
);
?>
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):
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
if (isset
($_REQUEST['artistid']) &&
is_numeric($_REQUEST['artistid'])) {
$artistid = $_REQUEST['artistid'];
} else {
$artistid = 1;
}
$dom = $query2xml->getXML(
"SELECT * FROM artist WHERE artistid = $artistid",
array(
'rootTag' => 'favorite_artist',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name',
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
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.
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$artistid = $_REQUEST['artistid'];
$dom = $query2xml->getXML(
array(
'data' => array(
":$artistid"
),
'query' => 'SELECT * FROM artist WHERE artistid = ?'
),
array(
'rootTag' => 'favorite_artist',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name',
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
The resulting XML data is identical in both cases (given that artistid was submitted as 1):
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artist>
<artist>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<music_genre>Soul</music_genre>
</artist>
</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:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
false,
array(
'idColumn' => false,
'rowTag' => '__tables',
'rootTag' => 'music_store',
'elements' => array(
'artists' => array(
'rootTag' => 'artists',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'sql' => 'SELECT * FROM artist',
'elements' => array(
'*'
)
),
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'sql' => 'SELECT * FROM album',
'elements' => array(
'*'
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
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:
<?xml version="1.0" encoding="UTF-8"?>
<music_store>
<artists>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
</artist>
</artists>
<albums>
<album>
<albumid>1</albumid>
<artist_id>1</artist_id>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
</album>
<album>
<albumid>2</albumid>
<artist_id>1</artist_id>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
</album>
<album>
<albumid>3</albumid>
<artist_id>2</artist_id>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
</album>
</albums>
</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:
array(
'COLUMN1',
'COLUMN2'
);
This might result in XML data like this:
<COLUMN1>this was the contents of COLUMN1</COLUMN1>
<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
array(
'ELEMENT1' => 'COLUMN1',
'ELEMENT2' => 'COLUMN2'
);
This would make the same data appear like this:
<ELEMENT1>this was the contents of COLUMN1</ELEMENT1>
<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':
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name',
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
This results in the following XML data:
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<music_genre>Soul</music_genre>
</artist>
<artist>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<music_genre>Soul</music_genre>
</artist>
<artist>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<music_genre>Country and Soul</music_genre>
</artist>
</favorite_artists>
The following example demonstrates the usage of all different types:
<?php
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/Callback.php';
require_once 'MDB2.php';
class Utils
{
function trim($record, $columnName)
{
return trim($record[$columnName]);
}
function getPublishedYearCentury($record)
{
return floor($record['published_year']/
100);
}
}
class ToLowerCallback implements XML_Query2XML_Callback
{
private $_columnName = '';
public function __construct($columnName)
{
$this->_columnName = $columnName;
}
public function execute(array $record)
{
return strtolower($record[$this->_columnName]);
}
}
$dom = $query2xml->getXML(
"SELECT
*
FROM
sale,
store,
album
WHERE
sale.store_id = store.storeid
AND
sale.album_id = album.albumid
AND
sale.timestamp < '2005-06-01'",
array(
'rootTag' => 'sales',
'idColumn' => 'saleid',
'rowTag' => 'sale',
'elements' => array(
'saleid',
'sale_timestamp' => 'timestamp',
'static' => ':some static text',
'album_century' => '#Utils::getPublishedYearCentury()',
'album_title' => '?#Utils::trim(title)',
'album_comment' => new ToLowerCallback('comment'),
'storeid',
'store_building1' => '?&building_xmldata',
'store_building2' => '?=building_xmldata',
'store_building3' => '?^building_xmldata'
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
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:
<?xml version="1.0" encoding="UTF-8"?>
<sales>
<sale>
<saleid>1</saleid>
<sale_timestamp>2005-05-25 07:32:00</sale_timestamp>
<static>some static text</static>
<now>1187498966</now>
<album_century>19</album_century>
<album_title>New World Order</album_title>
<album_comment>the best ever!</album_comment>
<storeid>1</storeid>
<store_building1>
<building>
<floors>4</floors>
<elevators>2</elevators>
<square_meters>3200</square_meters>
</building>
</store_building1>
<store_building2>< ![CDATA[<building><floors>4</floors><elevators>2</elevators><square_meters>3200</square_meters></building>]] ></store_building2>
<store_building3>PGJ1aWxkaW5nPjxmbG9vcnM+NDwvZmxvb3JzPjxlbGV2YXRvcnM+MjwvZWxldmF0b3JzPjxzcXVhcmVfbWV0ZXJzPjMyMDA8L3NxdWFyZV9tZXRlcnM+PC9idWlsZGluZz4=</store_building3>
</sale>
<sale>
<saleid>11</saleid>
<sale_timestamp>2005-05-25 07:23:00</sale_timestamp>
<static>some static text</static>
<now>1187498966</now>
<album_century>19</album_century>
<album_title>Curtis</album_title>
<album_comment>that man's got somthin' to say</album_comment>
<storeid>2</storeid>
<store_building1>
<building>
<floors>2</floors>
<elevators>1</elevators>
<square_meters>400</square_meters>
</building>
</store_building1>
<store_building2>< ![CDATA[<building><floors>2</floors><elevators>1</elevators><square_meters>400</square_meters></building>]] ></store_building2>
<store_building3>PGJ1aWxkaW5nPjxmbG9vcnM+MjwvZmxvb3JzPjxlbGV2YXRvcnM+MTwvZWxldmF0b3JzPjxzcXVhcmVfbWV0ZXJzPjQwMDwvc3F1YXJlX21ldGVycz48L2J1aWxkaW5nPg==</store_building3>
</sale>
</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':
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name' => array(
'value' => 'name',
'attributes' => array(
'type' => ':full_name'
)
),
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
This results in the following XML data:
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<name type="full_name">Curtis Mayfield</name>
<birth_year>1920</birth_year>
<music_genre>Soul</music_genre>
</artist>
<artist>
<name type="full_name">Isaac Hayes</name>
<birth_year>1942</birth_year>
<music_genre>Soul</music_genre>
</artist>
<artist>
<name type="full_name">Ray Charles</name>
<birth_year>1930</birth_year>
<music_genre>Country and Soul</music_genre>
</artist>
</favorite_artists>
Here is another little example:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array('albumid', 'title', 'published_year')
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
This results in the following XML data:
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
</album>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
</album>
</albums>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
</album>
</albums>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
<albums />
</artist>
</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
<artist>
<name>Curtis Mayfield</name>
<album>
<name>New World Order</name>
</album>
</artist>
<artist>
<name>Curtis Mayfield</name>
<album>
<name>Curits</name>
</album>
</artist>
but rather
<artist>
<name>Curtis Mayfield</name>
<albums>
<album>
<name>New World Order</name>
</album>
<albums>
<name>Curtis