Generating xml file by reading data from MySQL table in PHP

Create XML from database rows

Create each record as a child node of the XML document. Keep database access parameterized where user input is involved and let the XML API build the document structure.

<?php
$xml = new SimpleXMLElement('<students/>');
$stmt = $dbo->query('SELECT id, name, class, mark FROM student');

foreach ($stmt as $row) {
    $student = $xml->addChild('student');
    $student->addChild('id', (string) $row['id']);
    $student->addChild('name', (string) $row['name']);
    $student->addChild('class', (string) $row['class']);
    $student->addChild('mark', (string) $row['mark']);
}

echo $xml->asXML();
?>

Current example first; the detailed examples below are retained for additional learning and comparison.

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('<students/>');
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>";
	$student=$e->addChild('student');
	$student->addChild('id', (string) $row['id']);
	$student->addChild('name', (string) $row['name']);
	$student->addChild('class', (string) $row['class']);
	$student->addChild('mark', (string) $row['mark']);
}
}else{
print_r($dbo->errorInfo());
}
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('<students/>');
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>";
	$student=$e->addChild('student');
	$student->addChild('id', (string) $row['id']);
	$student->addChild('name', (string) $row['name']);
	$student->addChild('class', (string) $row['class']);
	$student->addChild('mark', (string) $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
// Use appropriate server permissions; avoid making the file world-writable. 
?>

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

Create XML from database rows

Writing XML data to File

Subhendu Mohapatra