Home » XML » XML_sql2xml » Manual

Introduction

Introduction – Simple transformations

Example database

In this tutorial, the examples refers to this database tables:

The typical using

Let's start with an example using the default options. The new instance is bind to an DSN, so you have only to provide an SQL query. The instance fetches the result automatically; in $xmlstring you found the XML representation of the result set.

The simplest example

<?php
require_once "XML/sql2xml.php";
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$xmlstring = $sql2xmlclass->getxml("select * from bands");
?>

The content of $xmlstring based on the DB tables above is:

Transformations based on Join queries

If your query result base on joined tables, a nested XML data structure can represent how the DBMS joins the tables. To enable or to disable this behavoir use setOptions() with the option key 'nested'. The default value is TRUE - the nesting is enabled.

Nested result set

<?php
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$xmlstring = $sql2xmlclass->getxml("select * from bands left join albums on bands.id = bandsID");
?>

The generated XML output in $xmlstring:

If you disable the nesting, the XML structure of rows is flat.

Unnested result sets

<?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");
?>

XML output:

Introduction

Introduction – Passing the data to transform

Overview

There are three ways to pass the data for transforming to the object:

• with a direct query This way you see in the first part of the introduction already.
• with an existing DB_result object If you need the full power of the PEAR::DB or PEAR::MDB API, you should choose this way.
• with an array This is not directly an SQL to XML transformation; if you pass an indice array to the instance, the keys of the array are transformed into XML tags, their values into the tag content. This feature his helpful for adding data to an XML transformed result set.

Direct query

This behavoir does connecting to the DBMS, quering and fetching he result automatically. XML_sql2xml requires an valid DSN as construtor parameter. The query has to be passed to the getXML() or add() method.

Take a look into the first part of the introduction for examples.

Passing an DB_Result object

PEAR::DB and PEAR::MDB return the result set of a query as DB_result object. You have to provide a DB_common instance to the construtor and the DB_result instance to getXML() or add().

Passing a DB_Result object

<?php
require_once "XML/sql2xml.php";
require_once "DB.php";

$db = db::connect("mysql://username:password@localhost/xmltest");
$result = $db->query("select * from bands");

$sql2xmlclass = new xml_sql2xml($db);
$xmlstring = $sql2xmlclass->getxml($result);
?>

The XML output in $xmlstring is equal to the example in "The typical using".

This way is the only one, if you want to benefit from all the features of the database APIs.

Passing an array

If you pass an nested array to getXML() or add(), it will be transformed into an XML document.

Passing an array

<?php
require_once "XML/sql2xml.php";

$sql2xmlclass = new xml_sql2xml();

$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);
?>

The XML output in $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>

Passing more then one result set

You can call add() several result sets to XML document. Just call the method for every result set; if you have added all result sets, call getXml() without any arguments to get the XML document.

Adding result sets

<?php
$sql2xmlclass->add("select * from bands");
$sql2xmlclass->add("select * from albums");
$xmlstring= $sql2xmlclass->getxml();
?>

Introduction

Introduction – Using XPath

Why XPath support?

The XPath support was introduced to provide an access to the result set after doing the SQL query. This allows further proccessing of the result set without quering the database again.

XPath is a W3-Standard and for further information about that, ask your preferred XSL/XML book, or http://www.w3.org/.

Passing an XPath expression

XML_sql2xml provides two functions for querying the result set: getXpathValue() and getXpathChildValues(). Both expect a XPath query and return the result as string or array.

getXpathValue

<?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");
?>

$xmlstring contains:

getXpathChildValues

<?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']");
?>

$xmlstring contains:

Mixing SQL and XPath query

You can insert an XPath query into an SQL query. In the example, first a SQL query is done, in the second a SQL query done again - but the parameter for bandsID is taken from the XPath expression in the curly braces. This expression is processed on the result set of the first SQL query.

Mixed query

<?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();
?>

XML_sql2xml::XML_sql2xml

XML_sql2xml::XML_sql2xml – Constructor

Synopsis

void XML_sql2xml::XML_sql2xml ( mixed $dsn = null , string $root="root" )

Description

The Constructor can take a Pear::DB Data Source Name (DSN) and will then connect to the database; or a PEAR::DB object handle, if you already connected the database before. Providing sql-strings will not work.

If you provide only a DSN, you have to add a DB_result object, SQL statement or an array later.

The $root parameter is used, if you want to provide another name for your root-tag than <root>. If you give an empty string (""), there will be no root element created here, but only when you add a resultset, array or SQL Statement. And the first tag of this result is used as the root tag.

Parameter

• mixed $dsn - PEAR::DB "data source name" or DB_common object
• string $root - the name of the XML-root element.

Note

This function can be called statically.

See

XML_sql2xml::add()

Example

Using XML_sql2xml()

<?php
$sql2xml = new xml_sql2xml("mysql://root@localhost/xmltest");
$sql2xml->Add("select * from bands");
?>

XML_sql2xml::add

XML_sql2xml::add – General method for adding new result sets

Synopsis

void XML_sql2xml::add ( string $resultset , mixed $params = null )

Description

General method for adding new result sets to the object. Give a SQL statement, a PEAR::DB_result object or an array as input parameter and the method calls the appropriate method for this input.

Parameter

• string $resultset - SQL statement, an DB_result object or an array
• mixed $params - parameters for the following functions

Note

This function can not be called statically.

See

XML_sql2xml::addResult(), XML_sql2xml::addSql(), XML_sql2xml::addArray(), XML_sql2xml::addXmlFile()

Example

Using add()

<?php
$sql2xml->Add("select * from bands");
?>

XML_sql2xml::addXmlFile

XML_sql2xml::addXmlFile – Adds a XML file

Synopsis

void XML_sql2xml::addXmlFile ( string $file , mixed $xpath = null )

Description

Adds the content of a XML file on the same level as a normal result set (mostly just below <root>).

Parameter

• string $file - file name
• mixed $xpath - either a string with the XPath expression or an array with the keys "xpath"=>XPath expression and "root"=>tag/subtag/etc, which are the tags to be inserted before the result.

Note

This function can not be called statically.

See

XML_sql2xml::add()

XML_sql2xml::addXmlString

XML_sql2xml::addXmlString – Adds XML string

Synopsis

void XML_sql2xml::addXmlString ( string $string , mixed $xpath = null )

Description

Adds the content of a xml-string on the same level as a normal result set (mostly just below <root>)

Parameter

• string $string - XML string
• mixed $xpath - either a string with the XPath expression or an array with the keys "xpath"=>XPath expression and "root"=>tag/subtag/etc, which are the tags to be inserted before the result.

Note

This function can not be called statically.

See

XML_sql2xml::add()

XML_sql2xml::addResult

XML_sql2xml::addResult – Adds an PEAR::DB result set

Synopsis

void XML_sql2xml::addResult ( Object $result )

Description

Adds an additional PEAR::DB_result result set

Parameter

• Object $result - a DB_result object

Note

This function can not be called statically.

See

XML_sql2xml::add()

XML_sql2xml::addSql

XML_sql2xml::addSql – Adds an result set from a SQL statement

Synopsis

void XML_sql2xml::addSql ( string $sql )

Description

Adds an aditional result set generated from an SQL statement. The function executes the statement and transform it into XML. You have to pass an DSN to the constructor.

Parameter

• string $sql - a string containing an SQL statement.

Note

This function can not be called statically.

See

XML_sql2xml::XML_sql2xml(), XML_sql2xml::add()

XML_sql2xml::addArray

XML_sql2xml::addArray – Adds an aditional result set from an array

Synopsis

void XML_sql2xml::addArray ( array $array )

Description

Adds an aditional result set generated from an array. The XML represents the nesting of the array.

Parameter

• array $array - data array

Note

This function can not be called statically.

See

XML_sql2xml::add()

XML_sql2xml::getXML

XML_sql2xml::getXML – Returns a XML representation of the result set.

Synopsis

string XML_sql2xml::getXML ( mixed $result = null )

Description

Returns an XML string with a XML representation of the result set. The result set can be directly provided here, or if you need more than one in your XML, then you have to provide each of them with add() before you call getXML(), but the last one can also be provided here.

Parameter

• mixed $result - a DB_result object from a DB query or a SQL query

Return value

string - the XML string

Note

This function can not be called statically.

XML_sql2xml::getXMLObject

XML_sql2xml::getXMLObject – return an XML DomDocument object

Synopsis

Object XML_sql2xml::getXMLObject ( mixed $result = null )

Description

Returns an XML DomDocument object with a XML representation of the result sets. The result set can be directly provided here, or if you need more than one in your XML, then you have to provide each of them with add() before you call getXMLObject(), but the last one can also be provided here.

Parameter

• mixed $result - a DB_result object from a DB query

Return value

Object - DomDocument object

Note

This function can not be called statically.

XML_sql2xml::setOptions

XML_sql2xml::setOptions – set options for the instance

Synopsis

void XML_sql2xml::setOptions ( array $options , constant $delete = false )

Description

This method sets the options for the class instance.

Parameter

• array $options - array of options. The array key defines the option to set. Avaible options are:
  • boolean $options['nested'] - if TRUE the result of an join query will be nested. The default value is FALSE.
  • boolean $options['tagNameRow'] - name for the row mark up tag. Default is >row<
  • boolean $options['tagNameResult'] - name for the result-set mark up tag. Default is >result<
• constant $delete - the old sub options should be deleted

Note

This function can not be called statically.

XML_sql2xml::setEncoding

XML_sql2xml::setEncoding – set encoding charset

Synopsis

void XML_sql2xml::setEncoding ( string $encoding_from="ISO-8859-1" , string $encoding_to="UTF-8" )

Description

Sets the encoding for the db2xml transformation. The $encoding_from value depends on your database system; check the DBMS manual which encoding your system use to deliever result sets.

The $encoding_to sets the charset for the created XML document.

Parameter

• string $encoding_from - encoding to transform from
• string $encoding_to - encoding to transform to

Note

This function can not be called statically.

XML_sql2xml::getXpathValue

XML_sql2xml::getXpathValue – return match of a XPath expression

Synopsis

mixed XML_sql2xml::getXpathValue ( string $expr )

Description

Returns the content of the first match of the XPath expression.

Parameter

• string $expr - XPath expression

Return value

mixed - content of the evaluated XPath expression

Note

This function can not be called statically.

See

XML_sql2xml::getXpathChildValues()

XML_sql2xml::getXpathChildValues

XML_sql2xml::getXpathChildValues – return child tags from match of a XPath expression

Synopsis

array XML_sql2xml::getXpathChildValues ( string $expr )

Description

Returns the values from the child tags from the first match of the XPath.

Parameter

• string $expr - XPath expression

Return value

array - the content of the child tags. The child tag name is assigned as key, the content as value.

Note

This function can not be called statically.

See

XML_sql2xml::getXpathValue()