Generating xml file by reading data from MySQL table in PHP

We will collect the each records by using Mysql query and PDO. We will store all our output in string and then we can write to a file with .xml extension or print to webpage by using htmlspecialchars() function. You can read more on PHP & XML here.
Demo of the output in XML format
$dbo : Database connection object inside config.php file

Here is the code.

<?Php
header('Content-Type: text/xml');
require "config.php"; // database connection file

$e = new SimpleXMLElement('<detils/>');
if($stmt = $dbo->query("SELECT id, name ,class, mark FROM student")){

foreach ($stmt as $row ) {
	//echo $row['id'],$row['name'],$row['class'].$row['mark']."<br>";
	$add=$e->addChild('student');
	$add=$e->addChild('id', $row['id']);
	$add=$e->addChild('name', $row['name']);
	$add=$e->addChild('class',$row['class']);
	$add=$e->addChild('mark', $row['mark']);
}
}else{
echo $connection->error;
}
echo $e->asXML(); 
?>

Using MySQLi database connection and SimpleXMLElement to addChild()

<?Php
header('Content-Type: text/xml');
require "config.php"; // database connection file

$e = new SimpleXMLElement('<detils/>');
if($stmt = $connection->query("SELECT id, name ,class, mark FROM student")){

while ($row = $stmt->fetch_assoc()) {
	//echo $row['id'],$row['name'],$row['class'].$row['mark']."<br>";
	$add=$e->addChild('student');
	$add=$e->addChild('id', $row['id']);
	$add=$e->addChild('name', $row['name']);
	$add=$e->addChild('class',$row['class']);
	$add=$e->addChild('mark', $row['mark']);
}
}else{
echo $connection->error;
}
echo $e->asXML(); 
?>

Sample output ( few records ) is here

<detils>
<student/>
<id>1</id>
<name>John Deo</name>
<class>Four</class>
<mark>75</mark>
<student/>
<id>2</id>
<name>Max Ruin</name>
<class>Three</class>
<mark>85</mark>
<student/>
<id>3</id>
<name>Arnold</name>
<class>Three</class>
<mark>55</mark>
<student/>
----
-----
-----
</detils>

Writing XML data to File

<?Php
require "config.php"; // Connect to database

//////////////////////////////////////
$sql="SELECT * FROM student"; 
$str ="<?xml version='1.0' encoding='UTF-8'?>n<student>";

foreach ($dbo->query($sql) as $row) {
$str .= "\n<details>\n\t\t\t<id>$row[id]</id>\n\t\t\t<name>$row[name]</name> ";

$str .= "\n\t\t\t  <class>$row[class]</class>\n</details>";
}
$str.= "\n</student>";
//$str=nl2br($str);
//echo htmlspecialchars($str); // remove this line if you are writing to file
echo $str;

/// Write to file ////////////

$file_name="test_file.xml";               // file name
$fp = fopen ($file_name, "w");  
// Open the file in write mode, if file does not exist then it will be created.
fwrite ($fp,$str);          // entering data to the file
fclose ($fp);               // closing the file pointer
chmod($file_name,0777); 
?>

Read more on displaying records
Download the student table here
XML XML Children

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Xml file from database

Writing XML data to File

Subhendu Mohapatra