PHP mysql query result to xml

Question

I want to print my mysql results to xml. This is what I've tried so far:

include('dbconnect.php');

$sql = "SELECT verse_id, verse_title, verse_content, lang FROM verses WHERE lang = 'English'";
$stmt = $conn->prepare($sql);
$stmt->execute();

$set = array();

while($r = $stmt->fetchAll(PDO::FETCH_ASSOC)){
    $set = $r;  
}

$xml = new SimpleXMLElement('<root/>');
array_walk_recursive($set, array($xml, 'addChild'));
print $xml->asXML();


?>

But it's displaying like this:

<1>verse_id<27>verse_title<"Peace I leave with you; my peace I give you. I do not give to you as the world gives. Do not let your hearts be troubled and do not be afraid. >verse_contentlang<2>

I want to display like this:

 <verse>
 <verse_id>1</verse_id>
 <verse_title>John 3:16</verse_title>
 <verse_content>For God so loved the world...</verse_content>
 <lang>English</lang>
 </verse>

I don't know what's wrong but if you know how to do this and can help, I'd appreciate it.

Answer 1

you can use DOMDocument instead of SimpleXMLElement and set the value of formatOutput to true:

$doc = new DOMDocument('1.0', 'UTF-8');
$doc->formatOutput = true;

---

Example

<?php

// "Create" the document.
$xml = new DOMDocument( "1.0", "UTF-8" );
$xml->formatOutput = true;

// Create some elements.
$xml_album = $xml->createElement( "Album" );
$xml_track = $xml->createElement( "Track", "The ninth symphony" );

// Set the attributes.
$xml_track->setAttribute( "length", "0:01:15" );
$xml_track->setAttribute( "bitrate", "64kb/s" );
$xml_track->setAttribute( "channels", "2" );

// Create another element, just to show you can add any (realistic to computer) number of sublevels.
$xml_note = $xml->createElement( "Note", "The last symphony composed by Ludwig van Beethoven." );

// Append the whole bunch.
$xml_track->appendChild( $xml_note );
$xml_album->appendChild( $xml_track );

// Repeat the above with some different values..
$xml_track = $xml->createElement( "Track", "Highway Blues" );

$xml_track->setAttribute( "length", "0:01:33" );
$xml_track->setAttribute( "bitrate", "64kb/s" );
$xml_track->setAttribute( "channels", "2" );
$xml_album->appendChild( $xml_track );

$xml->appendChild( $xml_album );
// Parse the XML.
print '<pre>' . htmlentities($xml->saveXML()) . '</pre>';
?>

Output