create xml from mysql data using php

Question

I have this code:

<?php
header('Content-type: text/xml');

$xmlout = "<?xml version=\"1.0\" ?>\n";
$xmlout .= "<persons>\n";

$db = new PDO('mysql:host=localhost;dbname=xxx','root','');
$stmt = $db->prepare("select * from users");
$stmt->execute();
while($row = $stmt->fetch()){
    $xmlout .= "\t<person>\n";
    $xmlout .= "\t\t<id>".$row['id']."</id>\n";
    $xmlout .= "\t\t<username>".$row['username']."</username>\n";
    $xmlout .= "\t\t<password>".$row['password']."</password>\n";
    $xmlout .= "\t\t<realname>".$row['realname']."</realname>\n";
    $xmlout .= "\t\t<surname>".$row['surname']."</surname>\n";
    $xmlout .= "\t\t<email>".$row['email']."</email>\n";
    $xmlout .= "\t\t<created>".$row['created']."</created>\n";
    $xmlout .= "\t\t<admin>".$row['admin']."</admin>\n";
    $xmlout .= "\t</person>\n";
}

$xmlout .= "</persons>";
echo $xmlout;
?>

and it doesn't work, it have this error:

error on line 2 at column 1: Extra content at the end of the document Below is a rendering of the page up to the first error.

Does anybody know, where is problem?

You shouldn't create XML manually, you should use something like [SimpleXML](http://cz1.php.net/manual/en/book.simplexml.php) — Jay Blanchard, Apr 04 '17 at 13:05
Possible duplicate of [XML Parse Error - Extra content at the end of the document](http://stackoverflow.com/questions/16972737/xml-parse-error-extra-content-at-the-end-of-the-document) — Mittul At TechnoBrave, Apr 04 '17 at 13:06
I remove ?> or do exit and it doesn't help... And I do this manually because I am beginner in xml and this way seems to me as the easiest way — Adéla, Apr 04 '17 at 13:14
Actually XMLWriter is an API for exactly that kind of job. SimpleXML is more for reading simple, small XML structures. — ThW, Apr 04 '17 at 13:28
you can have mysql server do this for you as well https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_xml — Robert, Apr 05 '17 at 02:24

score 0 · Answer 1 · answered Apr 04 '17 at 13:05

0

What is the \t (tab) for? Is there a specific reason you are manually declaring it in the code? Have you tried removing it?

Also, try building the XML string before you set the header, then echo out the string.

answered Apr 04 '17 at 13:05

Robert DeBoer

1,715
2
16
26

Yeah, I tried to delete, but it's not problem in this – Adéla Apr 04 '17 at 13:18

score 0 · Answer 2 · answered Apr 05 '17 at 02:19

Consider using DOMDocument class instead of string concatenation to build XML. Possibly the special space and tab characters are conflicting with header output. DOM will pretty print output with its formatOutput method.

header('Content-type: text/xml');

// INITIALIZE DOM OBJECT
$dom = new DOMDocument('1.0', 'UTF-8');
$dom->formatOutput = true;            
$dom->preserveWhiteSpace = false;

// CREATE ROOT AND APPEND TO DOCUMENT
$xmlRoot = $dom->createElement("persons");
$xmlRoot = $dom->appendChild($xmlRoot);

// QUERY DATABASE
$db = new PDO('mysql:host=localhost;dbname=xxx','root','');
$stmt = $db->prepare("select * from users");
$stmt->execute();

// FETCH ROWS ITERATIVELY
while($row = $stmt->fetch()){
     // APPEND PERSON AS CHILD OF ROOT
     $personNode = $xmlRoot->appendChild($dom->createElement('person'));
     // APPEND CHILDREN TO PERSON         
     $personNode->appendChild($dom->createElement('id', $row['id']));
     $personNode->appendChild($dom->createElement('username', $row['username']));
     $personNode->appendChild($dom->createElement('password', $row['password']));
     $personNode->appendChild($dom->createElement('realname', $row['realname']));
     $personNode->appendChild($dom->createElement('surname', $row['surname']));
     $personNode->appendChild($dom->createElement('email', $row['email']));
     $personNode->appendChild($dom->createElement('created', $row['created']));
     $personNode->appendChild($dom->createElement('admin', $row['admin']));
}
$stmt = null; 
$db = null;

// OUTPUT TO SCREEN
echo $dom->saveXML();

create xml from mysql data using php

2 Answers2