Insert XML Data to MySql Table
by Vincy. Last modified on July 14th, 2022.
Programmatically importing XML data into a MySQL database will be handy in many a situations. In this tutorial, we are going to learn how to insert data from XML file to the database using PHP.
The XML nodes are considered as the columns of the database table and so the data of each node are the values to be inserted into the table.
In this example, I have an input.xml file containing item tags with child nodes named as title, description, links and keywords. This input file is loaded to get the file handle object to parse the XML data.
Then, the PHP code will iterate each of the parsed item to get its child node and its data. With this data MySQL insert query is created and executed to copy XML data to the database.
Input XML Data
The following XML is the input for our example program to insert XML data into the database table. This file contains 3 item XML elements with the child nodes title, link, description and keywords tags.
As the child nodes are treated as the columns of the database table, I have created the database table columns with the name of these child nodes. While parsing this input XML the data inside these child nodes will be inserted into the database to the appropriate column.
<?xml version="1.0" encoding="UTF-8"?>
<items>
<item>
<title>PHP Shopping Cart with PayPal Payment Gateway Integration</title>
<link>https://phppot.com/php/php-shopping-cart-with-paypal-payment-gateway-integration/</link>
<description>Shopping cart checkout with payment gateway integration (PayPal) is a most wanted article for the PHP shopping cart coders. In this article, we are going to integrate PayPal payment gateway with the shopping cart.</description>
<keywords>Shopping,Cart</keywords>
</item>
<item>
<title>Responsive Contact Form with PHP</title>
<link>https://phppot.com/php/responsive-contact-form-with-php/</link>
<description>The responsive contact form is designed to fit various viewport in different width. I used CSS media queries to design this responsive contact form.</description>
<keywords>Contact-Form, Responsive</keywords>
</item>
<item>
<title>Loading Dynamic Content on a Bootstrap Modal using jQuery</title>
<link>https://phppot.com/jquery/loading-dynamic-content-on-a-bootstrap-modal-using-jquery/</link>
<description>Modal window can be shown in various ways by using jQuery, Bootstrap and others. In this tutorial, we are going to show the Bootstrap modal. </description>
<keywords>Modal-Window, Bootstrap</keywords>
</item>
</items>
and the SQL script for creating the database table is,
CREATE TABLE IF NOT EXISTS `tbl_tutorials` (
`item_id` int(11) NOT NULL,
`title` varchar(100) NOT NULL,
`link` varchar(100) NOT NULL,
`description` varchar(400) NOT NULL,
`keywords` varchar(50) NOT NULL
);
Parse XML and Insert Data to MySQL using PHP
In this PHP code, we use simple XML parsing to load the input XML file to create the file handle. Using this file handle the XML items will be start iterated to read the child nodes. Then I create the MySQL insert query by using the data read from the XML.
After inserting the data, a message will be shown to the user about how many rows are newly added into the database. In case of any error occurrences, then the error message will be shown to the user about the cause of the error.
<?php
$conn = mysqli_connect("localhost", "root", "test", "phpsamples");
$affectedRow = 0;
$xml = simplexml_load_file("input.xml") or die("Error: Cannot create object");
foreach ($xml->children() as $row) {
$title = $row->title;
$link = $row->link;
$description = $row->description;
$keywords = $row->keywords;
$sql = "INSERT INTO tbl_tutorials(title,link,description,keywords) VALUES ('" . $title . "','" . $link . "','" . $description . "','" . $keywords . "')";
$result = mysqli_query($conn, $sql);
if (! empty($result)) {
$affectedRow ++;
} else {
$error_message = mysqli_error($conn) . "\n";
}
}
?>
<h2>Insert XML Data to MySql Table Output</h2>
<?php
if ($affectedRow > 0) {
$message = $affectedRow . " records inserted";
} else {
$message = "No records inserted";
}
?>
Insert XML Data into MySql Table Output
This code checks the condition for the response text created during the process of inserting XML data into the database table. This response text is to acknowledge the user about the result of the MySQL insert.
<style>
body {
max-width: 550px;
font-family: Arial;
}
.affected-row {
background: #cae4ca;
padding: 10px;
margin-bottom: 20px;
border: #bdd6bd 1px solid;
border-radius: 2px;
color: #6e716e;
}
.error-message {
background: #eac0c0;
padding: 10px;
margin-bottom: 20px;
border: #dab2b2 1px solid;
border-radius: 2px;
color: #5d5b5b;
}
</style>
<div class="affected-row">
<?php echo $message; ?>
</div>
<?php if (! empty($error_message)) { ?>
<div class="error-message">
<?php echo nl2br($error_message); ?>
</div>
<?php } ?>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.