A little tutorial/howto for XML_sql2xml

Table of Contents

What's this all about ?
And what's the advantage of this ?
What does it ?
How to get it
API-Documentation
How to use it
  Prerequisites
  The most simple example
      Remarks..
  Example with a left join query
  Another Example with a left join query but not nested
  How to add an array
  How to add more than one resultset
  How to get other tag-names than <row> and <result>
  What's this xpath stuff ?
  How to give your own table-relations-structure
  How to use db_result Object instead of a sql-string / pear::db object instead of dsn
  Extending sql2xml
    A more complex example (Using sql2xml_ext)
What's missing now
The End

What's this all about ?

Today, very useful stuff can be done with xml (xsl transformation, data-exchange, xml-rpc, etc..), but a lot of information is still stored in relational databases. And furthermore, it's not very easy to store relational data only into xml (or at least, i didn't find out how). Therefore i wrote this little class to transform data from a database into xml on the fly.

And what's the advantage of this ?

As I said, you can still store and retrieve your data in databases with the power of sql or use legacy stuff lying around. But you can also use the advantages of xml/xsl in your php-scripts.

What does it ?

It just takes an sql-query, a pear::db_result or an array and gives you back a xml string or object representing the data. You get a more or less decent result with just the basic settings, but it's also highly configurable, so you can almost get what you want. And furthermore, if you have joined queries, this class tries to detect the relationship and gives you back a nested xml out of that. This does not always work right, but you can customize the relationship between the tables, as well. The automatic relationship detection does only work with mysql at the moment. But the class itself can be used with any db supported by the pear database abstraction class. see below for some real examples...

How to get it

Download the latest pear-stuff from the php-cvs.... If you don't know, how to do that, go to http://www.php.net/anoncvs.php. Or you can download the class here. But it's maybe not the same version as the one from the cvs. But actually both should work.
You can also watch at the class code directly here:
sql2xml.phps
sql2xml_ext.phps

API-Documentation

For details about the methods, see the API-Documentation, generated with PHPDoc.

How to use it

Prerequisites

You need to have compiled php with --with-domxml. Otherwise this class will not work. And you also need the PEAR-libraries in your include-path, since sql2xml relies on that (actually only on the DB modules..)
Be aware, that the sql2xml class needs a tableInfo function in PEAR::DB to be fully operational. This function will only be available after with 4.0.6 or if you use the pear code from the cvs. And at the moment, the tableInfo-method is only implemented for mysql.
The sql2xml class tries to simulate this function, if it doesn't exist for your RDBMS, but that does not really work for joined tables (it can't nest the xml output this way.). I'll come to that later in this tutorial

For all the examples in this tutorial, we use the following database. The values are meaningless :) A mysql-dump of these tables is also available. 

mysql> select * from bands;
+----+--------------+------------+-------------+-------------+
| id | name         | birth_year | birth_place | genre       |
+----+--------------+------------+-------------+-------------+
|  1 | The Blabbers |       1998 | London      | Rock'n'Roll |
|  2 | Only Stupids |       1997 | New York    | Hip Hop     |
+----+--------------+------------+-------------+-------------+

mysql> select * from albums;
+----+---------+------------------+------+-----------------+
| id | bandsID | title            | year | comment         |
+----+---------+------------------+------+-----------------+
|  1 |       1 | BlaBla           | 1998 | Their first one |
|  2 |       1 | More Talks       | 2000 | The second one  |
|  3 |       2 | All your base... | 1999 | The Classic     |
+----+---------+------------------+------+-----------------+

The most simple example

Let's start with an example. The most simple one, with no joins in the query and just using the default values.
In the first line, we include the class-file. This should be in your include-path, but that's maybe obvious :)
In the second line, we initiate the XML_sql2xml class. As parameter, we give a string according to the pear::DB dsn ("data source name") specifications. With this, we don't have to make a db-link by ourself, because sql2xml handles that. But you can also provide a DB-Object, if you already connected to the database before, or nothing, if you do not want to give sql-queries to sql2xml, but only pear::db_result-Objects or just Arrays.
And in the last line, we finally give the sql-query to the class and we get the xmlstring representing your data.
Remarks..
There's a second parameter you can give to the constructor. As you see in the below example, the root element of the xml-doc is called "root". Maybe you don't like that, so you give the name of the root-element as a second parameter to the constructor, eg: $sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest","anotherrootname");

Or, if you provide an empty string as the second parameter, it will not even create a special root-element, but it will take <result> as the root element. But be aware, that if you want to add more than one resultset to the same xml, you need a special root-element. See "How to add more than one resultset" for details about that and look also at "How to get other tag-names than <row> and <result>" for how to change the other tag names (like "result","row", etc..)

If you don't want the xml as string, but as a DomDocument Object, just call $sql2xmlclass->getXmlObject("query") instead of $sql2xmlclass->getXml().

example1.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$xmlstring = $sql2xmlclass->getxml("select * from bands");
?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <result>
            <row>
                <id>1</id>
                <name>The Blabbers</name>
                <birth_year>1998</birth_year>
                <birth_place>London</birth_place>
                <genre>Rock'n'Roll</genre>
            </row>
            <row>
                <id>2</id>
                <name>Only Stupids</name>
                <birth_year>1997</birth_year>
                <birth_place>New York</birth_place>
                <genre>Hip Hop</genre>
            </row>
        </result>
    </root>
'

Example with a left join query

In this example we use a more complicated query. It joins the 2 tables with a simple left join, so we can see which band published which albums. As you can see in the result, the albums are automagically nested into the elements of the corresponfing band-tags. Sql2xml tries to find out, which data belongs to which in the parent table. As said above, this works at the moment only with mysql and only reliable if you don't have joined more than two tables. How to give correct results without mysql or with more than two tables, see another example below.

example2.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$xmlstring = $sql2xmlclass->getxml("select * from bands left join albums on bands.id = bandsID");
?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <result>
            <row>
                <id>1</id>
                <name>The Blabbers</name>
                <birth_year>1998</birth_year>
                <birth_place>London</birth_place>
                <genre>Rock'n'Roll</genre>
                <row>
                    <id>1</id>
                    <bandsID>1</bandsID>
                    <title>BlaBla</title>
                    <year>1998</year>
                    <comment>Their first one</comment>
                </row>
                <row>
                    <id>2</id>
                    <bandsID>1</bandsID>
                    <title>More Talks</title>
                    <year>2000</year>
                    <comment>The second one</comment>
                </row>
            </row>
            <row>
                <id>2</id>
                <name>Only Stupids</name>
                <birth_year>1997</birth_year>
                <birth_place>New York</birth_place>
                <genre>Hip Hop</genre>
                <row>
                    <id>3</id>
                    <bandsID>2</bandsID>
                    <title>All your base...</title>
                    <year>1999</year>
                    <comment>The Classic</comment>
                </row>
            </row>
        </result>
    </root>
'

Another Example with a left join query but not nested

If you don't like the nested feature of Sql2xml, you can turn that of and you get a flat view of your query. Just set this options with $sql2xmlclass->SetOptions(array(nested=>False)) before you call getxml.

example3.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$options = array(nested => False);
$sql2xmlclass->SetOptions($options);
$xmlstring = $sql2xmlclass->getxml("select * from bands left join albums on bands.id = bandsID");
?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <result>
            <row>
                <id>1</id>
                <name>The Blabbers</name>
                <birth_year>1998</birth_year>
                <birth_place>London</birth_place>
                <genre>Rock'n'Roll</genre>
                <id>1</id>
                <bandsID>1</bandsID>
                <title>BlaBla</title>
                <year>1998</year>
                <comment>Their first one</comment>
            </row>
            <row>
                <id>1</id>
                <name>The Blabbers</name>
                <birth_year>1998</birth_year>
                <birth_place>London</birth_place>
                <genre>Rock'n'Roll</genre>
                <id>2</id>
                <bandsID>1</bandsID>
                <title>More Talks</title>
                <year>2000</year>
                <comment>The second one</comment>
            </row>
            <row>
                <id>2</id>
                <name>Only Stupids</name>
                <birth_year>1997</birth_year>
                <birth_place>New York</birth_place>
                <genre>Hip Hop</genre>
                <id>3</id>
                <bandsID>2</bandsID>
                <title>All your base...</title>
                <year>1999</year>
                <comment>The Classic</comment>
            </row>
        </result>
    </root>
'

How to add an array

Adding an associative-array to your xml-tree is as easy as adding an sql-statement. You just create an array like the one in the example and pass that as the parameter in $sql2xmlclass->getxml(). The code automatically detects if it's array or a query string (or a db_result ...).
You can pass almost any associative-array, even multidimensional ones and you should get a decent xml-representation out of it. For renaming the tags, the same rules as with a sql-query apply. See "How to give your own table-relations-structure" for that. And furthermore, the addArray function uses the same insertElements functions as the DB-part, so you can use them for both (See "Extending sql2xml" for that).

example5.php
<?php
include_once("XML/sql2xml.php");

$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");

$array = array (
            array("name"=>"The Blabbers",
                  "birth_year"=>"1998",
                  "birth_place"=>"London",
                  "genre"=>"Rock'n'Roll"),
            array("name"=>"Only Stupids",
                  "birth_year"=>"1997",
                  "birth_place"=>"New York",
                  "genre"=>"hiphop")
);

$xmlstring = $sql2xmlclass->getxml($array);

?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <result>
            <row>
                <name>The Blabbers</name>
                <birth_year>1998</birth_year>
                <birth_place>London</birth_place>
                <genre>Rock'n'Roll</genre>
            </row>
            <row>
                <name>Only Stupids</name>
                <birth_year>1997</birth_year>
                <birth_place>New York</birth_place>
                <genre>hiphop</genre>
            </row>
        </result>
    </root>
'

How to add more than one resultset

Instead of directly passing the sql in the $sql2xmlclass->getxml() you add each query/array first with $sql2xmlclass->add(). It doesn't matter if it's an array a sql-string or a pear::db_result, the add-method calls the right functions for you.
The last result could also be passed to the class with the $sql2xmlclass->getxml() method like before, if you like that more.

be aware, if you want to add more than one resultset, you should not give an empty string as the second parameter (the name of the root-element) in the constructor, otherwise you get only the first resultset back.

example6.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$sql2xmlclass->add("select * from bands");
$sql2xmlclass->add("select * from albums");
$xmlstring= $sql2xmlclass->getxml();
?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <result>
            <row>
                <id>1</id>
                <name>The Blabbers</name>
                <birth_year>1998</birth_year>
                <birth_place>London</birth_place>
                <genre>Rock'n'Roll</genre>
            </row>
            <row>
                <id>2</id>
                <name>Only Stupids</name>
                <birth_year>1997</birth_year>
                <birth_place>New York</birth_place>
                <genre>Hip Hop</genre>
            </row>
        </result>
        <result>
            <row>
                <id>1</id>
                <bandsID>1</bandsID>
                <title>BlaBla</title>
                <year>1998</year>
                <comment>Their first one</comment>
            </row>
            <row>
                <id>2</id>
                <bandsID>1</bandsID>
                <title>More Talks</title>
                <year>2000</year>
                <comment>The second one</comment>
            </row>
            <row>
                <id>3</id>
                <bandsID>2</bandsID>
                <title>All your base...</title>
                <year>1999</year>
                <comment>The Classic</comment>
            </row>
        </result>
    </root>
'

How to get other tag-names than <row> and <result>

Just provide an options-Array with the elements "tagNameRow" and/or "tagNameResult" to setOptions(). See the example for details.
If you want to change the root-tag-name, you have to provide it in the constructor.

If you want more complex xmls than this base class provides, see "Extending sql2xml" for more information.

example9.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest","YourRootName");
$options = array (  tagNameRow      => "YourRowName",
                    tagNameResult   => "YourResultName"
        );
$sql2xmlclass->SetOptions($options);
$xmlstring = $sql2xmlclass->getxml("select * from bands");
?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <YourRootName>
        <YourResultName>
            <YourRowName>
                <id>1</id>
                <name>The Blabbers</name>
                <birth_year>1998</birth_year>
                <birth_place>London</birth_place>
                <genre>Rock'n'Roll</genre>
            </YourRowName>
            <YourRowName>
                <id>2</id>
                <name>Only Stupids</name>
                <birth_year>1997</birth_year>
                <birth_place>New York</birth_place>
                <genre>Hip Hop</genre>
            </YourRowName>
        </YourResultName>
    </YourRootName>
'

What's this xpath stuff ?

After you added a query, you normaly have no direct access to the result of this query, but maybe you need some values of it for further processing. Instead of making the same query again, you can get the values with an xpath-expression. Xpath is a W3-Standard and for further information about that, ask your preferred XSL/XML book, or http://www.w3.org.

The functions getXpathValue() and getXpathChildValues() make it relatively easy to get values from the stuff already inserted into the xml. Just see the examples, how it roughly works. Furthermore you can apply xpath-expression directly in sql-queries and when you're importing another xml-file.
In the first example below you can see, how we get the name of the band with an id = 2.
In the second example, we get an array back with all the information for the band with an id = 2.
You can also add xpath-expressions directly to the sql-query to refer to values got with a query before. In the third example, we want additionally all the albums from the band called "The Blabbers". Therefore we have to know the ID of this band, which we get with the xpath-expression in the curly brackets (but only if we added a query before containing this information...).
For adding xml-files, you can also apply xpath-expressions. In the fourth example, from bands.xml we only want the band with id = 2 included, therefore we supply this xpath as a second parameter to add(). Since the full path gets lost with this xpath-expression, we also provide an additional path, which is prepended before the actual result. This root-parameter is optional, as is the xpath-parameter. Without that one, the whole xml-file is taken.

exampleXpath.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$sql2xmlclass->add("select * from bands");
$xmlstring  = $sql2xmlclass->getXpathValue("/root/result/row[id = '2']/name");
?>
This example will produce:
$xmlstring = 'Only Stupids'

exampleXpath2.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$sql2xmlclass->add("select * from bands");
$xmlstring  = $sql2xmlclass->getXpathChildValues("/root/result/row[id = '2']");
?>
This example will produce:
$xmlstring = '
Array
(
    [id] => 2
    [name] => Only Stupids
    [birth_year] => 1997
    [birth_place] => New York
    [genre] => Hip Hop
)
'

exampleXpath3.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$sql2xmlclass->add("select * from bands");
$sql2xmlclass->add("select * from albums where bandsID = {/root/result/row[name = 'The Blabbers']/id}");
$xmlstring = $sql2xmlclass->getxml();
?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <result>
            <row>
                <id>1</id>
                <name>The Blabbers</name>
                <birth_year>1998</birth_year>
                <birth_place>London</birth_place>
                <genre>Rock'n'Roll</genre>
            </row>
            <row>
                <id>2</id>
                <name>Only Stupids</name>
                <birth_year>1997</birth_year>
                <birth_place>New York</birth_place>
                <genre>Hip Hop</genre>
            </row>
        </result>
        <result>
            <row>
                <id>1</id>
                <bandsID>1</bandsID>
                <title>BlaBla</title>
                <year>1998</year>
                <comment>Their first one</comment>
            </row>
            <row>
                <id>2</id>
                <bandsID>1</bandsID>
                <title>More Talks</title>
                <year>2000</year>
                <comment>The second one</comment>
            </row>
        </result>
    </root>
'

exampleXpath4.php
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml();
$sql2xmlclass->add("bands.xml",array("xpath"=>"/bands/band[id='2']",
                                     "root" =>"/bands"   )
                  );
$xmlstring = $sql2xmlclass->getxml();
?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <bands>             <band>                 <id>1</id>                 <name>The Blabbers</name>                 <birth_year>1998</birth_year>                 <birth_place>London</birth_place>                 <genre>Rock'n'Roll</genre>             </band>             <band>                 <id>2</id>                 <name>Only Stupids</name>                 <birth_year>1997</birth_year>                 <birth_place>New York</birth_place>                 <genre>Hip Hop</genre>             </band>         </bands>
    </root>
'

How to give your own table-relations-structure

I'm not very satisfied how you can do this at the moment, so I maybe rewrite some stuff in the code and write then this part of the tutorial.

How to use db_result Object instead of a sql-string / pear::db object instead of dsn

Since the main methods automatically detect, if you pass a db_result Object or a sql-string / a pear::db object or a "data source name" you can use the same methods. See the example for details. It does the same as example 1.

example8.php
<?php
include_once("XML/sql2xml.php");
include_once("DB.php");
$db = db::connect("mysql://username:password@localhost/xmltest");
$sql2xmlclass = new xml_sql2xml($db);
$result = $db->query("select * from bands");
$xmlstring = $sql2xmlclass->getxml($result);
?>

Extending sql2xml

Since it's almost not possible, to include every wished feature and to keep the base sql2xml rather small, it was decided to have the possibility to extend sql2xml with some customizable functions. sql2xml_ext is such an extended class.

If you want now to customize the sql2xml class to get a xml as you wish, you mainly rewrite the following functions (look at the source code for the parameter definitions).

This functions are called everytime the corresponding "event" occcurs. This means for example, if we add another resultset to the xml, insertNewResult is called.

The following very simple example of extending the base class, just adds the values in your db-fields not as a new element to the xml, but as an attribute to the "row"-element. This is by the way, how MSSQL returns db-results as xml. You could also enhance this function, that it returns binary-data base64 encoded (also the way MSSQL does it...)

class_attr.php
<?php
require_once ("XML/sql2xml.php");
class XML_sql2xml_attr extends XML_sql2xml {
    function insertNewElement($parent, $res, $key, &$tableInfo, &$subrow) {
            $subrow=$parent->set_attribute($tableInfo[$key]["name"], $this->xml_encode($res[$key]));
    }
}
?>

And the code from example 1 would then look like the following.

example_attr_class.php
<?php
include_once("XML/sql2xml_attr.php");
$sql2xmlclass = new xml_sql2xml_attr("mysql://username:password@localhost/xmltest");
$xmlstring = $sql2xmlclass->getxml("select * from bands");
?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <result>
            <row id="1" name="The Blabbers" birth_year="1998" birth_place="London" genre="Rock'n'Roll"/>
            <row id="2" name="Only Stupids" birth_year="1997" birth_place="New York" genre="Hip Hop"/>
        </result>
    </root>
'

A more complex example (Using sql2xml_ext)
In one of our projects, we needed a more complex xml, the sql2xml_ext class.
The insertNewResult()-function in the base-class just adds a <result>-tag to the xml-tree, but sql2xml_ext rewrites the function, so it takes the tablename instead of just "result" and adds an attribute type="resultset". The same goes for insertNewRow() where we also also add the table-name plus an attribute type=row and an unique ID, and so on.

We provide also some additional options to the class. This options are in an array called "user_options". This way, it's ensured that this variables can be accessed from all functions used in this class, because the array $user_options is a class-variable. The base-class sql2xml doesn't use any variables defined in user_options, so you can be sure, that you don't break anything if you extend this class.

In sql2xml_ext, we have two additional user_options-variables. "xml_seperator" and "element_id".
xml_seperator is used in insertNewElement() to seperate field names with "_" (in this example) and to make seperate tags out of it (this keeps the database somewhat flat, but you get nicer looking xmls :)).
element_id looks for an id-field in insertNewRow() and adds an attribute ID to the row-tag containing the table-name and the id of this field.

example_ext.php
<?php
include_once("XML/sql2xml_ext.php");

$options= array( user_options => array (xml_seperator =>"_",
                                        element_id => "id"),
           );
$sql2xmlclass = new xml_sql2xml_ext("mysql://username:password@localhost/xmltest");
$sql2xmlclass->SetOptions($options);        
$xmlstring = $sql2xmlclass->getxml("select * from bands");

?>
This example will produce:
$xmlstring = '
<?xml version="1.0"?>
    <root>
        <bands type="resultset">
            <bands type="row" ID="bands1">
                <id>1</id>
                <name>The Blabbers</name>
                <birth>
                    <year>1998</year>
                    <place>London</place>
                </birth>
                <genre>Rock'n'Roll</genre>
            </bands>
            <bands type="row" ID="bands2">
                <id>2</id>
                <name>Only Stupids</name>
                <birth>
                    <year>1997</year>
                    <place>New York</place>
                </birth>
                <genre>Hip Hop</genre>
            </bands>
        </bands>
    </root>
'

What's missing now

The End

For questions and remarks, contact me chregu@nomad.ch.

$Id: tutorial.html,v 1.2 2001/08/29 07:53:28 chregu Exp $