Turn SQL into XML with PHP

Document options Document options requiring JavaScript are not displayed My developerWorks needs you! Connect to your technical community Rate this page Help us improve this content

Level: Intermediate

Vikram Vaswani, Founder, Melonfire

24 Jul 2007

Ever wished for an easy way to transform SQL result sets into XML? It's a PEAR package named XML_Query2XML, and it provides a comprehensive framework to efficiently turn the results of a database query into a customizable XML document. This article introduces the package, and demonstrates useful real-world applications, including using it with XSL and XPath, combining it with data from external Web services, and creating database dump files.

Introduction

Maybe you've already heard of PEAR, the PHP Extension and Application Repository. This community-driven project aims to provide an extensive, open source library of high-quality code, to assist PHP developers in rapid application development. Similar in concept to Perl's CPAN repository, PEAR has long been my first stop for interesting and useful PHP+XML widgets. Some of these include the XML_Serializer class, which is handy to serialize PHP data structures as XML objects; the XML_XUL class, which provides an API to construct Mozilla XUL applications; and the XML_SVG class, which offers methods to programmatically construct vector graphics in the SVG format.

In this article, I will introduce you to yet another member of PEAR's XML section, the XML_Query2XML class. This class provides an API to quickly and efficiently turn SQL result sets into well-formed XML. With a little creativity, it's easy to convert this output into other formats through XSL transformations, or integrate it with other XML-based applications.

Back to top

Install required software

The XML_Query2XML package is actively developed and maintained by Lukas Feiler, and released to the PHP community under an LGPL license. It requires PHP 5.0 (or later). The easiest way to install it is with the automated PEAR installer, which should have been included by default with your PHP build. To install it, simply issue the following command at your shell prompt:

shell>pear install XML_Query2XML

The PEAR installer will now connect to the PEAR package server, download the package, and install it to the appropriate location on your system.

To install the package by hand, visit its home page on the PEAR Web site, download the package archive, and manually uncompress the files to the desired location. Note that manual installation process presupposes some knowledge of PEAR's package organization structure.

At this stage, you should also be aware of some other dependencies:

1. XML_Query2XML uses any one of the DB, MDB2 or ADOdb database abstraction layers to communicate with the target RDBMS, and hence requires one of these abstraction layers to be present and correctly installed, together with the appropriate database driver. The examples in this article use the MDB2 abstraction layer, which is part of the PEAR package tree, together with its MySQL driver MDB2_Driver_mysql. As described previously, you can use the PEAR automated installer to install both these packages; alternatively, you can download them from the PEAR Web site.
2. The examples in this article make use of MySQL's sample world database, which provides various pre-populated and linked tables containing city and country data. Instructions for obtaining and setting up the world database may be obtained from the Resources section of this article.
3. The examples in this article require a PHP build that has support for PHP's DOM, XSL and SimpleXML functions. These functions are enabled by default in PHP 5.x
4. A working knowledge of PHP's DOM and SimpleXML functions and XML, XPath and XSL technologies is assumed.

See Resources for information and download links for these different components.

All examples in this article have been tested with XML_Query2XML Version 1.2.1.

Back to top

Convert SQL to XML

With all the required components successfully installed, you can begin to explore XML_Query2XML with the following simple PHP script:

<?php
// include required files
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
    // initialize Query2XML object
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));
    
    // generate SQL query
    // get results as XML
    $sql = "SELECT * FROM Country";
    $xml = $q2x->getFlatXML($sql);
    
    // send output to browser
    header('Content-Type: text/xml');
    $xml->formatOutput = true;
    echo $xml->saveXML();
} catch (Exception $e) {
    echo $e->getMessage();
}
?>
        

This script demonstrates basic usage of the XML_Query2XML class. To begin, the script includes the XML_Query2XML and MDB2 class files, and then initializes an instance of the MDB2 abstraction layer through its factory() method. This method accepts a DSN as input, containing information on the RDBMS type, the RDBMS user name and password, and the target database name. The resulting MDB2 instance is then used to initialize an XML_Query2XML instance, represented by the $q2x object.

Once you construct your DSN and create an XML_Query2XML object instance, it's time to actually execute an SQL query on the RDBMS and turn the result into XML. This is accomplished through the getFlatXML() method of XML_Query2XML, which is commonly used for simple SELECT-type queries. The output of this method is a well-formed XML document, with the SQL result set encoded within it. Here's what it looks like:

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <row>
    <code>AFG</code>
    <name>Afghanistan</name>
    <continent>Asia</continent>
    <region>Southern and Central Asia</region>
    <surfacearea>652090.00</surfacearea>
    <indepyear>1919</indepyear>
    <population>22720000</population>
    <lifeexpectancy>45.9</lifeexpectancy>
    <gnp>5976.00</gnp>
    <gnpold></gnpold>
    <localname>Afganistan/Afqanestan</localname>
    <governmentform>Islamic Emirate</governmentform>
    <headofstate>Mohammad Omar</headofstate>
    <capital>1</capital>
    <code2>AF</code2>
  </row>
  <row>
    <code>NLD</code>
    <name>Netherlands</name>
    <continent>Europe</continent>
    <region>Western Europe</region>
    <surfacearea>41526.00</surfacearea>
    <indepyear>1581</indepyear>
    <population>15864000</population>
    <lifeexpectancy>78.3</lifeexpectancy>
    <gnp>371362.00</gnp>
    <gnpold>360478.00</gnpold>
    <localname>Nederland</localname>
    <governmentform>Constitutional Monarchy</governmentform>
    <headofstate>Beatrix</headofstate>
    <capital>5</capital>
    <code2>NL</code2>
  </row>
  <row>
    <code>ANT</code>
    <name>Netherlands Antilles</name>
    <continent>North America</continent>
    <region>Caribbean</region>
    <surfacearea>800.00</surfacearea>
    <indepyear></indepyear>
    <population>217000</population>
    <lifeexpectancy>74.7</lifeexpectancy>
    <gnp>1941.00</gnp>
    <gnpold></gnpold>
    <localname>Nederlandse Antillen</localname>
    <governmentform>Nonmetropolitan Territory of 
    The Netherlands</governmentform>
    <headofstate>Beatrix</headofstate>
    <capital>33</capital>
    <code2>AN</code2>
  </row>
  ...
</root>
        

A close look at the output XML above reveals a clear structure. Each record from the SQL result set is represented as a <row> element, and individual fields of each record are nested within each corresponding <row>. The names of the nested elements correspond to the field names within the table being queried, while the document element — the root of the XML tree — is, aptly, named <root>.

Back to top

Transform XML output with XSL

Of course, generating XML from an SQL query is usually just half the job; the other half involves doing something with it. You can do many things with an XML document, but one of the most common involves use of an XSL Transformation to convert it into some other format, such as HTML or RSS. With this in mind, let's now put together a quick XSL stylesheet, to convert the XML output of Listing 2 into a simple HTML page.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:template match="/root">
        <html>
            <head>
                <style type="text/css">
                td { text-align: center; padding: 3px; }
                .head { font-style: italic; }
                </style>
            </head>
            <body>
                <table border="1">
                    <thead>
                        <tr>
                            <xsl:for-each select="row[1]/*">
                                <td class="head">
                                    <xsl:value-of select="local-name(.)"/>
                                </td>
                            </xsl:for-each>
                        </tr>
                    </thead>
                    <tbody>
                        <xsl:apply-templates/>
                    </tbody>
                </table>
            </body>
        </html>
    </xsl:template>

    <xsl:template match="row">
        <tr>
            <xsl:apply-templates/>
        </tr>
    </xsl:template>

    <xsl:template match="row/*">
        <td>
            <xsl:value-of select="."/>
        </td>
    </xsl:template>
</xsl:stylesheet>
        

In Listing 4 you see the revised PHP script, which now uses PHP's XSL functions to transform the output generated by XML_Query2XML:

<?php
// include required files
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
    // initalize Query2XML object
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));
    
    // generate SQL query
    // get results as XML
    $sql = "SELECT * FROM Country";
    $xml = $q2x->getFlatXML($sql);
    
    // read XSL stylesheet data
    $xsl = new DOMDocument;
    $xsl->load('country.xsl');
    
    // initialize XSLT engine
    $xslp = new XSLTProcessor;
    
    // attach XSL stylesheet object
    $xslp->importStyleSheet($xsl); 
    
    // perform transformation
    header('Content-Type: text/html');
    echo $xslp->transformToXML($xml);
} catch (Exception $e) {
    echo $e->getMessage();
}
?>
        

The first part of this script is similar to the Listing 1; it generates an XML document containing the results of the SQL query and stores this in $xml as a DOMDocument instance. Next, an instance of the XSLTProcessor class is initialized, and the XSL stylesheet is imported using the importStyleSheet() method of the class. The transformToXML() method, which accepts the source XML data as an input argument, is then used to transform the XML document into an HTML page, using the rules specified in the XSL stylesheet.

Figure 1 shows what the output looks like:

Back to top

Customize XML output

The getFlatXML() method demonstrated in previous examples is great when you're only interested in a quick SQL-to-XML conversion. However, when you want something more complicated — for example, to show certain result set fields as attributes rather than elements, or to define your own element names — you should reach for the getXML() method in XML_Query2XML. This method allows you to extensively customize the output XML, including its structure and style.

Here's an example in Listing 5:

<?php
// include required files
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
    // initalize Query2XML object
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));
    
    // generate SQL query
    // get results as XML
    $sql = "SELECT * FROM Country";
    $xml = $q2x->getXML($sql, array(
            'idColumn' => 'code',
            'rootTag' => 'countries',
            'rowTag' => 'country',
            'attributes' => array('code'),
            'elements' => array('name', 'continent', 'area' => 'surfacearea')
        )    
    );
    
    // send output to browser
    header('Content-Type: text/xml');
    $xml->formatOutput = true;
    echo $xml->saveXML();
} catch (Exception $e) {
    echo $e->getMessage();
}
?>
        

The getXML() method accepts two arguments: the SQL query to execute, and an array of options which define the format of the XML output. Table 1 tells you what each of the options in the previous listing mean:

Listing 6 shows what the output of the script looks like:

<?xml version="1.0" encoding="UTF-8"?>
<countries>
  <country code="AFG">
    <name>Afghanistan</name>
    <continent>Asia</continent>
    <area>652090.00</area>
  </country>
  <country code="NLD">
    <name>Netherlands</name>
    <continent>Europe</continent>
    <area>41526.00</area>
  </country>
  <country code="ANT">
    <name>Netherlands Antilles</name>
    <continent>North America</continent>
    <area>800.00</area>
  </country>
  ...
</countries>
        

Notice that this XML document, rather than containing all the fields from the SQL result set, contains only those specified in the elements and attributes arrays, and that the fields specified in the attributes array further appear as attributes, rather than as child nodes, of each <country> element.

You'll remember also that, by default, element and attribute names in the output XML default to the corresponding field names. However, when you use the getXML() method, you can alter these default names by specifying alternate values in the attributes and elements arrays, as key-value pairs. Case in point: the field named surfacearea in the SQL result set appears simply as the element <area> in the XML output.

For more examples of how you can customize the output of the getXML() method, take a look at the XML_Query2XML manual (see Resources).

Back to top

Work with SQL joins

XML_Query2XML also provides a framework for nesting the contents of one result set within another using XML. This feature is most commonly used when working with joins, or queries which are otherwise linked in some manner; it's also handy when you need to break a single large query into many smaller ones for performance reasons.

To better understand this, let's return to the world database and consider two of its tables, Country and City, which are linked to each other via the code foreign key.

Now, let's suppose you wanted to generate an XML document tree that nested multiple <city> elements within an outer <country> element. Let's further suppose that you wanted to restrict the output to only the five most-populated cities for each country, and that you wanted field values to be represented as attributes rather than elements. In short, let's suppose you were looking for an XML document like the sample below:

<?xml version="1.0" encoding="UTF-8"?>
<countries>
  <country code="IND" name="India" region="Southern and Central Asia">
    <cities>
      <city name="Mumbai (Bombay)" district="Maharashtra" population="10500000"/>
      <city .../>
      <city .../>
      <city .../>
      <city .../>
    </cities>
  </country>
  <country ...>
      ...
  </country>
  ...
</countries>
        

In Listing 8 is the code needed to generate such a nested XML document:

<?php
// include required files
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
    // initalize Query2XML object
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));
    
    // generate SQL query
    // get results as XML
    $sql_1 = "SELECT * FROM Country";
    $sql_2 = "SELECT * FROM City WHERE CountryCode = ? ORDER BY Population DESC LIMIT 5";
    $xml = $q2x->getXML($sql_1, array(
            'idColumn' => 'code',
            'rootTag' => 'countries',
            'rowTag' => 'country',
            'attributes' => array('code', 'name', 'continent'),
            'elements' => array('cities' => array(
                'sql' => array('data' => array('code'), 'query' => $sql_2),
                'idColumn' => 'id',
                'rootTag' => 'cities',
                'rowTag' => 'city',
                'attributes' => array('name','district','population'))
            )
        )    
    );
    
    // send output to browser
    header('Content-Type: text/xml');
    $xml->formatOutput = true;
    echo $xml->saveXML();
} catch (Exception $e) {
    echo $e->getMessage();
}
?>
        

The key thing to notice here is the elements array. Unlike in the Listing 5, where this array merely contained a list of result set fields to be displayed as elements, here it performs a far more complex function. It first defines a new element, <cities>, and links it to an options array containing key-value pairs. The only new key in this options array is the sql key, which defines the inner SQL query to run in order to populate the <cities> element.

It's worth your while to spend a few minutes to understand this sql key. This key is linked to an associative array, itself containing two keys:

• data, which specifies the fields to be imported from the outer SQL query
• query, which specifies the inner SQL query to run when populating the <cities> element

Notice that this second SQL query contains a question mark (?) placeholder — at run-time, this placeholder is replaced by the current value of the fields specified in the data array. Or, to use a concrete example, if a record returned by the outer query contains the value 'IND' for the field code, this value of 'IND' will subsequently be interpolated into the inner query, replacing the ? placeholder.

The genius of XML_Query2XML should now be clear. You can populate every elements array with a separate SQL query, thus allowing for SQL result sets to be nested to an unlimited depth. Further, because every elements array can refer to fields from the parent query, it's possible to create a series of chained queries (similar to an SQL join) which are linked to each other by specific named fields.

Here's what the output looks like:

<?xml version="1.0" encoding="UTF-8"?>
<countries>
  <country code="AFG" name="Afghanistan" continent="Asia">
    <cities>
      <city name="Kabul" district="Kabol" population="1780000"/>
      <city name="Qandahar" district="Qandahar" population="237500"/>
      <city name="Herat" district="Herat" population="186800"/>
      <city name="Mazar-e-Sharif" district="Balkh" population="127800"/>
    </cities>
  </country>
  <country code="NLD" name="Netherlands" continent="Europe">
    <cities>
      <city name="Amsterdam" district="Noord-Holland" population="731200"/>
      <city name="Rotterdam" district="Zuid-Holland" population="593321"/>
      <city name="Haag" district="Zuid-Holland" population="440900"/>
      <city name="Utrecht" district="Utrecht" population="234323"/>
      <city name="Eindhoven" district="Noord-Brabant" population="201843"/>
    </cities>
  </country>
  ...
</countries>
        

Having got this far, it's now a snap to generate a new XSL stylesheet to account for the new XML structure:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:template match="/countries">
        <html>
            <head>
                <style type="text/css">
                td { text-align: center; padding: 3px; }
                .head { font-style: italic; }
                </style>
            </head>
            <body>
                <xsl:for-each select="country">
                    <h2><xsl:value-of select="@name"/> - <xsl:value-of 
                    select="@continent"/></h2>
                    <table border="1">
                        <thead>
                            <tr>
                                <xsl:for-each select="cities/city[1]/@*">
                                <td class="head">
                                    <xsl:value-of select="name(.)"/>
                                </td>
                                </xsl:for-each>
                            </tr>
                        </thead>
                        <tbody>
                            <xsl:apply-templates/>
                        </tbody>
                    </table>
                </xsl:for-each>
            </body>
        </html>
    </xsl:template>

    <xsl:template match="cities/city">
      <tr>
        <xsl:for-each select="@*">
        <td>
            <xsl:value-of select="."/>
        </td>
        </xsl:for-each>
    </tr>
    </xsl:template>
</xsl:stylesheet>
        

Of course, you should also then revise the original PHP script to transform the generated XML using this stylesheet. The change, however, is trivial:

<?php
// include required files
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
    // initalize Query2XML object
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));
    
    // generate SQL query
    // get results as XML
    $sql_1 = "SELECT * FROM Country";
    $sql_2 = "SELECT * FROM City WHERE CountryCode = ? ORDER BY Population DESC LIMIT 5";
    $xml = $q2x->getXML($sql_1, array(
            'idColumn' => 'code',
            'rootTag' => 'countries',
            'rowTag' => 'country',
            'attributes' => array('code', 'name', 'continent'),
            'elements' => array('cities' => array(
                'sql' => array('data' => array('code'), 'query' => $sql_2),
                'idColumn' => 'id',
                'rootTag' => 'cities',
                'rowTag' => 'city',
                'attributes' => array('name','district','population'))
            )
        )    
    );
    
    // read XSL stylesheet data
    $xsl = new DOMDocument;
    $xsl->load('countries.xsl');
    
    // initialize XSLT engine
    $xslp = new XSLTProcessor;
    
    // attach XSL stylesheet object
    $xslp->importStyleSheet($xsl); 
    
    // perform transformation
    header('Content-Type: text/html');
    echo $xslp->transformToXML($xml);
} catch (Exception $e) {
    echo $e->getMessage();
}
?>
        

Figure 2 shows what the transformed XML looks like:

You can use this nesting capability in many ways, and XML_Query2XML also offers various options to tweak the XML output further. You can find detailed examples in the XML_Query2XML manual (see Resources).

Back to top

Filter SQL records with XPath

As you might imagine, it's fairly easy to constrain the output of the getXML() method to only those records that match certain constraints. You simply add the appropriate WHERE clause to the SQL query. An alternative is to use XPath constructs to create filtered subsets of the XML node tree and return them to the caller.

In Listing 12, look at a simple example of how to accomplish this, by revising Listing 11 and constraining the output XML to list only those countries and cities located in Europe, using an XPath condition:

<?php
// include required files
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
    // initialize Query2XML object
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));
    
    // generate SQL query
    // get results as XML
    $sql_1 = "SELECT * FROM Country";
    $sql_2 = "SELECT * FROM City WHERE CountryCode = ? ORDER BY Population DESC LIMIT 5";
    $xml = $q2x->getXML($sql_1, array(
            'idColumn' => 'code',
            'rootTag' => 'countries',
            'rowTag' => 'country',
            'attributes' => array('code', 'name', 'continent'),
            'elements' => array('cities' => array(
                'sql' => array('data' => array('code'), 'query' => $sql_2),
                'idColumn' => 'id',
                'rootTag' => 'cities',
                'rowTag' => 'city',
                'attributes' => array('name','district','population'))
            )
        )    
    );

    // now, further filter the XML using XPath
    // return only those <country> nodes which have the attribute 'continent=Europe'    
    // as a DOMNodeList
    $xpath = new DOMXPath($xml);
    $nodelist = $xpath->query("/countries/country[@continent='Europe']");
    
    // generate a new DOM tree using the XPath result set
    // create the root element
    // import each node from the node list and append to the new DOM tree
    $dom = new DOMDocument;
    $root = $dom->createElement('countries');
    $dom->appendChild($root);
    $x = 0;
    while ($node = $nodelist->item($x)) {
        $node = $dom->importNode($node, true);
        $root->appendChild($node);
        $x++;
    }
    
    // print XML
    header('Content-Type: text/xml');
    $dom->formatOutput = true;
    echo $dom->saveXML();
} catch (Exception $e) {
    echo $e->getMessage();
}
?>
        

The first segment of this script is as before — two nested SQL queries, with the inner one using data from the outer one to generate a list of country and city data. However, this time, instead of immediately printing the XML or passing it to an XSLT processor, a DOMXPath object is initialized, and a new DOMNodeList is created from the original XML tree. This DOMNodeList uses an XPath query to ensure that it contains only those <country> elements which have a continent attribute holding the value Europe. Once the DOMNodeList is created, a new DOMDocument is initialized and this DOMNodeList is imported into it, node by node, to generate a new XML document.

Listing 13 shows a snippet of the output:

<?xml version="1.0"?>
<countries>
  <country code="NLD" name="Netherlands" continent="Europe">
    <cities>
      <city name="Amsterdam" district="Noord-Holland" population="731200"/>
      <city name="Rotterdam" district="Zuid-Holland" population="593321"/>
      <city name="Haag" district="Zuid-Holland" population="440900"/>
      <city name="Utrecht" district="Utrecht" population="234323"/>
      <city name="Eindhoven" district="Noord-Brabant" population="201843"/>
    </cities>
  </country>
  <country code="ALB" name="Albania" continent="Europe">
    <cities>
      <city name="Tirana" district="Tirana" population="270000"/>
    </cities>
  </country>
  <country code="AND" name="Andorra" continent="Europe">
    <cities>
      <city name="Andorra la Vella" district="Andorra la Vella" population="21189"/>
    </cities>
  </country>
  <country code="BEL" name="Belgium" continent="Europe">
    <cities>
      <city name="Antwerpen" district="Antwerpen" population="446525"/>
      <city name="Gent" district="East Flanderi" population="224180"/>
      <city name="Charleroi" district="Hainaut" population="200827"/>
      <city name="Liège" district="Liège" population="185639"/>
      <city name="Bruxelles [Brussel]" district="Bryssel" population="133859"/>
    </cities>
  </country>
  ...
<countries>
        

Back to top

Merge data from multiple sources

In the real world of XML-based application development, it's unlikely that your XML document will hold information from only a single source. In addition to one or more SQL result sets, it might also contain data from disk files, from external Web services, and from the system's process table. To account for these situations, XML_Query2XML provides a way to integrate data from non-SQL sources in the XML returned by the getXML() method.

XML_Query2XML allows developers to define custom callback functions, which are called by specific elements within the output XML. These callback functions are expected to internally obtain the necessary data , turn it into XML, and return this XML (as DOMNode instances) to the caller, suitable for insertion at the appropriate point in the XML document tree. These callback functions must be preceded by a hash (#) symbol in the call to getXML(), and they will automatically receive the current SQL record as input.

You might ask if this is actually useful. The best way to demonstrate is with an example. First, suppose that you want to generate an XML document listing countries and their most-populated cities. You've already seen many examples that do just this. To make things more interesting, let's enhance this XML with the latitude and longitude of each named city, using data from the GeoNames Web service.

Listing 14 shows the code:

<?php
ini_set('max_execution_time', 120);
// include required files
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
    // initalize Query2XML object
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));
    
    // generate SQL query
    // get results as XML
    $sql = "SELECT Country.Code2 AS code, Country.Name AS country, City.Name AS city, 
        City.Population AS population FROM Country, City 
        WHERE Country.Code = City.CountryCode GROUP BY City.CountryCode 
        HAVING City.Population = MAX(City.Population) ORDER BY City.Population 
        DESC LIMIT 15";
    $xml = $q2x->getXML($sql, array(
            'idColumn' => 'code',
            'rootTag' => 'countries',
            'rowTag' => 'country',
            'attributes' => array('code', 'name' => 'country'),
            'elements' => array('city' => array (
                'elements' => array(
                    'name' => 'city',
                    'population',
                    'location' => '#getLocation'),
                )
            ),
        )    
    );
    
    // print XML
    header('Content-Type: text/html');
    $xml->formatOutput = true;
    print $xml->saveXML();
} catch (Exception $e) {
    echo $e->getMessage();
}

// function to get data from GeoNames Web service
// call GeoNames with country code and city name
// create XML document fragment with returned values
function getLocation($record) {
    // get data and format into SimpleXML object
    $sxml = simplexml_load_string(file_get_contents(
        "http://ws.geonames.org/search?maxRows=1&name=" . 
            urlencode(utf8_encode($record['city'])) . "&country=" . 
            urlencode(utf8_encode($record['code']))));
    
    // extract data from SimpleXML object
    // convert into DOMNode fragments
    $dom = new DOMDocument();
    // generate <lat> node
    $lat = $dom->createElement('lat');
    $lat->appendChild($dom->createTextNode($sxml->geoname{0}->lat));
    // generate <long> node
    $long = $dom->createElement('long');
    $long->appendChild($dom->createTextNode($sxml->geoname{0}->lng));
    return array($lat, $long);
}
?>
        

In Listing 14, the call to getXML() performs a SELECT query, which groups the various cities by their country code and then selects the one with the maximum population. This data is then turned into the following XML document (Listing 15):

<?xml version="1.0" encoding="UTF-8"?>
<countries>
  <country code="AW" name="Aruba">
    <city>
      <name>Oranjestad</name>
      <population>29034</population>
    </city>
  </country>
  ...
</countries>
        

The next task is to get the latitude and longitude of each city and insert it into the above document tree (see Listing 14). This information comes from the GeoNames Web service, which is accessible over REST and exposes a search() method that returns geographical information for a specified place name. A full description of this Web service is outside the scope of this article, but you can read more in the Resources for this article.

Listing 16 shows an example of a GeoNames response packet to a query for 'Berlin, Germany':

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<geonames>
 <totalResultsCount>807</totalResultsCount>
 <geoname>
  <name>Berlin</name>
  <lat>52.5166667</lat>
  <lng>13.4</lng>
  <geonameId>2950159</geonameId>
  <countryCode>DE</countryCode>
  <countryName>Germany</countryName>
  <fcl>P</fcl>
  <fcode>PPLC</fcode>
 </geoname>
</geonames>
        

As you can see, this response packet contains various bits of information about the specified location, including the data you're most interested in: its latitude and longitude.

Now, look closely at the call to getXML() in Listing 13. Notice that the location key of the options array is linked, not to a field from the query result set, but to a callback function named getLocation(). This means that every time getXML() processes a record from the SQL result set, it passes the getLocation() callback the same record, as an associative array of field-value pairs. The getLocation() method in turn uses REST to invoke the search() method of the GeoNames Web service, passes it the city and country name from the SQL record as parameters, and retrieves the response as a SimpleXML object. SimpleXML notation can then be used to drill down to the <lat> and <lng> elements from the response packet, convert these into two separate DOMNode instances, and pass them back to getXML() as an array for insertion into the tree.

At the end of the process, Listing 17 shows what the output XML looks like:

<?xml version="1.0" encoding="UTF-8"?>
<countries>
  <country code="IN" name="India">
    <city>
      <name>Mumbai (Bombay)</name>
      <population>10500000</population>
      <location>
        <lat>18.975</lat>
        <long>72.8258333</long>
      </location>
    </city>
  </country>
  <country code="KR" name="South Korea">
    <city>
      <name>Seoul</name>
      <population>9981619</population>
      <location>
        <lat>37.5663889</lat>
        <long>126.9997222</long>
      </location>
    </city>
  </country>
  ...
</countries>
        

As this example illustrates, the use of custom callback functions is an easy way to bring data from other sources into the XML output generated by getXML(). Listing 14 connected to an external Web service; you could just as easily have imported an external file, or the output of an XML-RPC call, into the final XML tree.

Back to top

Create database backups

Another useful application of XML_Query2XML involves dumping the contents of database tables into an XML-based format, for storage and backup purposes. The logic behind such a backup script is quite simple: obtain a list of tables from the database, iterate over this list and use commands such as DESC ? and SELECT * FROM ? SQL to extract the schema and records of each table respectively. If you followed along with this article, you might have already mentally mapped the getXML() method call needed to accomplish this.

To perform this task is somewhat more complicated than it appears at first glance, mostly because of certain limitations of the MDB2 abstraction layer: namely, the fact that it can't handle placeholders for column or table names in prepared queries. This makes it hard to use the DESC ? and SELECT * FROM ? queries referred to previously, as the MDB2 layer will simply generate an error when it encounters such queries.

What can you do now? Call on a little creativity, as seen in Listing 18:

<?php
ini_set('max_execution_time', 120);

// include required files
include 'XML/Query2XML.php';
include 'MDB2.php';

// set database name
$db = 'world';

try {
    // initialize Query2XML object
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/' . $db));

    // SQL query to get table list
    // note: this SQL query varies from database to database
    $sql = "SHOW TABLES";
    $xml = $q2x->getXML($sql, array(
                'idColumn' => false,
                'rootTag' => 'database',
                'rowTag' => 'table',
                'attributes' => array('name' => 'tables_in_' . $db))    
    );

    // get a list of all the <table> nodes
    $nodelist = $xml->getElementsByTagName("table");    
    
    // iterate over the nodes
    $x = 0;
    while ($node = $nodelist->item($x)) {
        // extract the table name
        $table = $node->attributes->getNamedItem('name')->nodeValue;
        
        // get table description
        // as DOM document
        // note: this SQL query varies from database to database
        $sql_1 = 'DESC ' . $table;
        $schema = $q2x->getXML($sql_1, array (
            'idColumn' => 'field',
            'rowTag' => 'define',
            'rootTag' => 'schema',
            'elements' => array('*'))
        );
        
        // get table contents
        // as another DOM document
        $sql_2 = 'SELECT * FROM ' . $table;
        $data = $q2x->getXML($sql_2, array (
            'idColumn' => false,
            'rowTag' => 'record',
            'rootTag' => 'data',
            'elements' => array('*'))
        );
        
        // iterate over the $schema DOM document
        // use XPath to get the <schema> node and all its children
        // import it into the main XML tree, under the corresponding <table> element
        // credit: Igor Kraus, http://www.php.net/simplexml for this suggestion
        $xpath = new DOMXPath($schema);
        $query = $xpath->query('//schema');
        for ($i = 0; $i < $query->length; $i++) {
            $xml->documentElement->childNodes->item($x)->appendChild(
                $xml->importNode($query->item($i), true));
        }
        
        // do the same for the $data DOM document
        $xpath = new DOMXPath($data);
        $query = $xpath->query('//data');
        for ($i = 0; $i < $query->length; $i++) {
            $xml->documentElement->childNodes->item($x)->appendChild(
                $xml->importNode($query->item($i), true));
        }
        
        // increment counter for the next run
        $x++;
    }
    
    // write output to disk
    // print success/error message
    $xml->formatOutput = true;
    if ($xml->save('/tmp/dump.xml')) {
        echo 'Data successfully saved!';
    } else {    
        echo 'Data could not be saved!';
    }
} catch (Exception $e) {
        echo $e->getMessage();    
}
?>
        

This looks fairly complicated, but it's actually quite simple:

1. First, obtain the names of all the tables in the current database. The SQL command to obtain this list varies from database to database, The script in Listing 18 uses MySQL's SHOW TABLES command, but this is not portable across different RDBMS. You will need to change if you use a different database system. The output of this command is an XML document, stored as $xml and looks like Listing 19:
   
   Listing 19. The first-stage XML output generated by Listing 18
   

   <?xml version="1.0" encoding="UTF-8"?> <database> <table name="City"/> <table name="Country"/> <table name="CountryLanguage"/> </database>

   
   
2. Next, use the getElementsByTagName() method to obtain a collection of all the <table> elements generated in the previous step, which is then processed in a loop. On each iteration of the loop, two new XML documents are created: $schema, which contains information on the field structure of the table (see Listing 20), and $data, which holds the actual records from the table (see Listing 21):
   
   Listing 20. The XML document containing the table dchema
   

   <?xml version="1.0" encoding="UTF-8"?> <schema> <define> <field>ID</field> <type>int(11)</type> <null>NO</null> <key>PRI</key> <default/> <extra>auto_increment</extra> </define> <define> <field>Name</field> <type>char(35)</type> <null>NO</null> <key/> <default/> <extra/> </define> <define> ... </define> </schema>

   
   
   
   Listing 21. The XML document containing the table records
   

   <?xml version="1.0" encoding="UTF-8"?> <data> <record> <id>1</id> <name>Kabul</name> <countrycode>AFG</countrycode> <district>Kabol</district> <population>1780000</population> </record> <record> <id>2</id> <name>Qandahar</name> <countrycode>AFG</countrycode> <district>Qandahar</district> <population>237500</population> </record> <record> ... </record> </data>

   
   
3. Continuing within the same loop iteration, import the two independent XML documents, $schema and $data, into the parent XML document $xml. XPath, which you've seen in previous examples, provides an easy way to extract XML node fragments from $schema and $xml; the importNode() method of the DOM extension takes care of the rest, by surgically inserting these fragments at the appropriate point of the main XML tree trunk.

   Listing 22 shows a snippet of the final output:

   
   
   Listing 22. The final XML output generated by Listing 18
   

   <?xml version="1.0" encoding="UTF-8"?> <database> <table name="City"> <schema> <define> <field>ID</field> <type>int(11)</type> <null>NO</null> <key>PRI</key> <default/> <extra>auto_increment</extra> </define> <define> <field>Name</field> <type>char(35)</type> <null>NO</null> <key/> <default/> <extra/> </define> ... </schema> <data> <record> <id>1</id> <name>Kabul</name> <countrycode>AFG</countrycode> <district>Kabol</district> <population>1780000</population> </record> <record> <id>2</id> <name>Qandahar</name> <countrycode>AFG</countrycode> <district>Qandahar</district> <population>237500</population> </record> ... </data> </table> <table> ... </table> </database>

   
   

Listing 23 offers another, more elegant solution by Lukas Feiler, developer of the XML_Query2XML class:

<?php
ini_set('max_execution_time', 120);

// credit: Lukas Feiler, http://www.lukasfeiler.com
// include files
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';

// initialize MDB abstraction layer
// load MDB manager
$mdb2 = MDB2::factory('mysql://root:pass@localhost/world');
$mdb2->loadModule('Manager');

// initialize Query2XML object
$q2x = XML_Query2XML::factory($mdb2);

// get table list
$tables = $mdb2->listTables();

// dynamically generate $options array
// once for each table
$elements = array();
for ($i=0; $i<count($tables); $i++) {
    $elements['table' . $i] = array(
        'rowTag' => 'table',
        'attributes' => array(
            'name' => ':' . $tables[$i]
        ),
        'elements' => array(
            'record' => array(
                'idColumn' => false,
                'sql' => 'SELECT * FROM ' . $tables[$i],
                'elements' => array(
                    '*'
                )
            )
        )
    );
}

// get data from tables as XML
$xml = $q2x->getXML(
    false,
    array(
        'idColumn' => false,
        'rowTag' => '__tables',
        'rootTag' => 'database',
        'elements' => $elements 
    )
);

// write output to disk
// print success/error message
$xml->formatOutput = true;
if ($xml->save('/tmp/dump.xml')) {
    echo 'Data successfully saved!';
} else {    
    echo 'Data could not be saved!';
}
?>
        

This solution first loads the MDB2 Manager module, and uses the listTables() method of that module to retrieve a list of all tables in the database in a database-independent manner. It then iterates over this table list, dynamically generating a new elements array on each iteration. Once all the tables are processed, a call to getXML(), with the dynamically generated elements array, produces an XML dump of the entire database and writes it to disk. Listing 24 shows a snippet of what you'd see in the output file:

<?xml version="1.0" encoding="UTF-8"?>
<database>
  <table name="city">
    <record>
      <id>1</id>
      <name>Kabul</name>
      <countrycode>AFG</countrycode>
      <district>Kabol</district>
      <population>1780000</population>
    </record>
    <record>
      <id>2</id>
      <name>Qandahar</name>
      <countrycode>AFG</countrycode>
      <district>Qandahar</district>
      <population>237500</population>
    </record>
    ...
  </table>
  <table name="country">
    <record>
      <code>AFG</code>
      <name>Afghanistan</name>
      <continent>Asia</continent>
      <region>Southern and Central Asia</region>
      <surfacearea>652090.00</surfacearea>
      <indepyear>1919</indepyear>
      <population>22720000</population>
      <lifeexpectancy>45.9</lifeexpectancy>
      <gnp>5976.00</gnp>
      <gnpold/>
      <localname>Afganistan/Afqanestan</localname>
      <governmentform>Islamic Emirate</governmentform>
      <headofstate>Mohammad Omar</headofstate>
      <capital>1</capital>
      <code2>AF</code2>
    </record>
    ...
  </table>
  <table>
  ...
  </table>
</database>
        

Back to top

Conclusion

Share this... Digg this story Post to del.icio.us Slashdot it!

As the previous listings illustrate, the XML_Query2XML package can do much more than simply format your SQL result sets as XML. It can serve as an enabler for a wide range of applications, ranging from simple SQL-to-HTML converters to tools that create complex XML documents from a wide range of input sources, including Web services, disk files, and multiple database systems. For all these reasons, it's a worthy addition to any PHP developer's toolkit. Try it the next time you need an interface between your PHP/XML application and your SQL database, and see for yourself!

Resources

• The XML_Query2XML manual: Learn to generate XML data from SQL queries in an excellent overview of the XML_Query2XML API.
  
  
• The GeoNames Web service: Read a description of this Web service that returns a list of postal codes and places for the placename/postalcode query as xml document .
  
  
• XML_Query2XML development and news: Track the latest info on Lukas Feiler's home page.
  
  
• More Pear packages: Find other PEAR packages related to PHP and XML development .
  
  
• XML area on developerWorks: Get the resources you need to advance your skills in the XML arena.
  
  
• IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
  
  
• XML technical library: See the developerWorks XML library for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks.
  
  
• developerWorks technical events and webcasts: Stay current with technology in these sessions.
  
  
• The technology bookstore: Browse for books on these and other technical topics.
  
  

• IBM trial software: Build your next development project with trial software available for download directly from developerWorks.
  
  
• The PEAR XML_Query2XML package: Download and start to transform info retrieved with one or more SQL SELECT queries into XML data.
  
  
• the PEAR MDB2 package: Download a portable merge of the PEAR DB and Metabase php database abstraction layers for a common API for all supported RDBMS.
  
  
• The MySQL driver: Download the driver for the PEAR MDB2 package.
  
  
• The example MySQL world database: Obtain instructions for to download and install sample country, city, and language data.
  
  
• IBM®DB2® Enterprise 9: Download a trial version of DB2 9 or DB2 Express-C 9, a no-charge version of DB2 Express 9 data server.

• developerWorks blogs: Check out these blogs and get involved in the developerWorks community.
  
  
• XML zone discussion forums: Participate in any of several XML-related discussions.
  
  

About the author

		Vikram Vaswani is the founder and CEO of Melonfire, a consulting services firm with special expertise in open-source tools and technologies. He is also the author of the books PHP Programming Solutions and How to do Everything with PHP and MySQL.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Back to top