Generating and creating an XML file from MySQL with PHP

Generating an XML file with data from a MySQL database using the inbuilt SimpleXMLElement class.

By using this class you can avoid formatting errors and issues than if you were to build from output into strings.

PDO connection and loop

The simple PDO MySQL connection with a SELECT loop:

$db = new PDO("mysql:host=127.0.0.1;dbname=test; charset=utf8", 'root', '');

$select = $db->query("SELECT `type`, `name`, `value`, `enabled` FROM `xml_data`;");
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    //Loop each row
}

Building the XML

Now with SimpleXMLElement() the XML can be built from within the loop:

$xml = new SimpleXMLElement('<objects/>');

$select = $db->query("SELECT `type`, `name`, `value`, `enabled` FROM `xml_data`;");
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    ($row['type'] === 'category') ? $obj = $xml->addChild('category') : $obj = $xml->addChild('tag');
    $obj->addAttribute('enabled', $row['enabled']);
    $obj->addChild('name', $row['name']);
    $obj->addChild('value', $row['value']);
}
$xml->asXML('the_xml_file_name.xml');

Header('Content-type: text/xml');
print($xml->asXML());

This doesn’t look like much but when you see the output, the addAttribute and addChild becomes more apparent.

This will output:

<?xml version="1.0"?>
<objects>
    <category enabled="1">
        <name>tools</name>
        <value>all</value>
    </category>
    <category enabled="1">
        <name>food</name>
        <value>all</value>
    </category>
    <category enabled="1">
        <name>books</name>
        <value>all</value>
    </category>
    <category enabled="0">
        <name>vehicle_parts</name>
        <value/>
    </category>
    <tag enabled="1">
        <name>container</name>
        <value>all</value>
    </tag>
    <tag enabled="1">
        <name>shelves</name>
        <value>all</value>
    </tag>
    <tag enabled="0">
        <name>locker</name>
        <value>all</value>
    </tag>
</objects>

Saving the XML to file

To save this as an XML file us asXML() :

$xml->asXML('the_xml_file_name.xml');

This will save the XML data into the file the_xml_file_name.xml