¿Cómo cargar datos XML en MySQL usando PHP?

En este artículo, vamos a almacenar datos presentes en un archivo XML en la base de datos MySQL usando PHP en el servidor XAMPP.

XML:

Ejemplo:

<?xml version="1.0" encoding="UTF-8"?>
<datas>
    <data>
        <id>1</id>
        <name>sravan</name>
    </data>
    <data>
        <id>2</id>
        <name>Ojaswi</name>
    </data>
    <data>
        <id>3</id>
        <name>Rohith</name>
    </data>
</datas>

Cargando archivo XML: Usaremos la función simplexml_load_file()

Sintaxis:

SimpleXMLElement simplexml_load_file( string $filename, 
        string $class_name = "SimpleXMLElement",
        int $options = 0, string $ns = "", 
        bool $is_prefix = FALSE )

Pasos para escribir y ejecutar código:

• Iniciar XAMPP

• Identifique la cantidad de atributos en el archivo xml y cree una tabla en XAMPP. Hay 4 atributos en el archivo XML (input.xml es el nombre del archivo). Estos son título, enlace. Descripción Palabras clave. El nombre de la base de datos es xmldata y el nombre de la tabla es xml

Nombre de archivo: entrada.xml

<?xml version="1.0" encoding="UTF-8"?>
<items>
    <item>
        <title>PHP DATABASE CONNECTION</title>
        <link>
https://www.geeksforgeeks.org/php-database-connection/
        </link>
        <description>
            The collection of related data is called a
            database. XAMPP stands for cross-platform,
            Apache, MySQL, PHP, and Perl. It is among
            the simple light-weight local servers for
            website development.
        </description>
        <keywords>PHP, XAMPP</keywords>
    </item>
 
    <item>
 
        <title>Screen density and Terminologies</title>
        <link>
https://www.geeksforgeeks.org/screen-density-and-its-terminologies/
        </link>
        <description>
            Screen Density is a calculation of the
            proportion of display character positions
            on the screen or an area of the screen
            containing something.
        </description>
        <keywords>software engineering</keywords>
    </item>
 
    <item>
        <title>DataProcessing vs DataCleaning</title>
        <link>
https://www.geeksforgeeks.org/difference-between-data-cleaning-and-data-processing/
        </link>
        <description>
            Data Processing: It is defined as Collection,
            manipulation, and processing of collected data
            for the required use. It is a task of converting
            data from a given form to a much more usable and
            desired form i.e. making it more meaningful and
            informative. Using Machine Learning algorithms,
            mathematical modelling and statistical knowledge,
            this entire process can be automated. This might
            seem to be simple but when it comes to really big
            organizations like Twitter, Facebook,
            Administrative bodies like Parliament, UNESCO and
            health sector
        </description>
        <keywords>Data Mining</keywords>
    </item>
 
    <item>
        <title>Predicting Air Quality Index</title>
        <link>
https://www.geeksforgeeks.org/predicting-air-quality-index-using-python/
        </link>
        <description>
            AQI: The air quality index is an index for
            reporting air quality on a daily basis. In
            other words, it is a measure of how air
            pollution affects one’s health within a
            short time period. The AQI is calculated
            based on the average concentration of a
            particular pollutant measured over a
            standard time interval. Generally, the
            time interval is 24 hours for most pollutants,
            8 hours for carbon monoxide and ozone.
        </description>
        <keywords>Machine Learning, Python</keywords>
    </item>
</items>

Nombre de archivo: index.php

<?php
 
// Connect to database
// Server - localhost
// Username - root
// Password - empty
// Database name = xmldata
$conn = mysqli_connect("localhost", "root", "", "xmldata");
 
$affectedRow = 0;
 
// Load xml file else check connection
$xml = simplexml_load_file("input.xml")
    or die("Error: Cannot create object");
 
// Assign values
foreach ($xml->children() as $row) {
    $title = $row->title;
    $link = $row->link;
    $description = $row->description;
    $keywords = $row->keywords;
     
    // SQL query to insert data into xml table
    $sql = "INSERT INTO xml(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";
    }
}
?>
 
<center><h2>GEEKS GOR GEEKS</h2></center>
<center><h1>XML Data storing in Database</h1></center>
<?php
if ($affectedRow > 0) {
    $message = $affectedRow . " records inserted";
} else {
    $message = "No records inserted";
}
 
?>
<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 } ?>

Pasos de ejecución:

1. Guarde 2 archivos en una carpeta en la ruta: xampp/htdocs/gfg

2. Escriba localhost/gfg/index.php y vea el resultado

producción

Ahora verifique los datos en xml almacenados en nuestra base de datos o no