Import XML file into MySQL using a simple PHP script

Question

I have tried so many help pages on multpl sites/forums and am still struggling here.

I am looking to simply import XML data from an online URL/file into a MySQL database.

The https://www.geeksforgeeks.org/how-to-load-xml-data-into-mysql-using-php/ worked with the sample data though the XML data feed I have to work with is structured differently.

This is the XML data feed I have...

<STOREITEMS>
        <PRODUCT ITEM='sku-01' NAME='This Is Title 1'>
            <STOCK>
                In Stock
            </STOCK>
        </PRODUCT>
        
        
        
        <PRODUCT ITEM='sku-02' NAME='This Is Title 2'>
            <STOCK>
                In Stock
            </STOCK>
        </PRODUCT>
        
        
        
        <PRODUCT ITEM='sku-03' NAME='This Is Title 3'>
            <STOCK>
                No Stock.
            </STOCK>
        </PRODUCT>
    </STOREITEMS>

Any and all help would be appreciated.

I have used the geekforgeeks code as below...

<?php
 
// Connect to database
// Server - localhost
// Username - root
// Password - empty
// Database name = xmldata
$conn = mysqli_connect("localhost", "root", "empty", "xmldata");
 
$affectedRow = 0;
 
// Load xml file else check connection
$xml = simplexml_load_file("xmltest.xml")
    or die("Error: Cannot create object");
 
// Assign values
foreach ($xml->children() as $row) {

    $product = $row->product;
    $stock = $row->stock;
     

    $sql = "INSERT INTO xtrader(
        SKU, stock) VALUES ('"
        . $product . "','" . $stock . "')";     
    $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 } ?>

Nothing is importing.

Answer 1

$product = $row->product;

There is no $row->product because the <PRODUCT> element is itself the row. You want the attribute named ITEM in that element:

$attributes = $row->attributes();
$sku = (string) $attributes['ITEM'];

Or:

$sku = (string) $row->attributes()['ITEM'];

Or just:

$sku = (string) $row['ITEM'];

Also note that $row->STOCK will get you everything in that attribute, including whitespace:

$stock = (string) $row->STOCK;
var_dump($stock);

Yields:

string(38) "
            In Stock
        "

This is probably not what you want, so trim() the excess:

$stock = trim((string) $row->STOCK);

Other concerns:

• Some of your items use No Stock and some use No Stock. with a period. If you base conditionals on this, you're gonna have a bad time. This value probably should be a simple integer representation, so the "no stock" condition would be simply <STOCK>0</STOCK>.
• If you're going to put the SKU and NAME in attributes, you might as well put the stock in an attribute too, like <PRODUCT ITEM='sku-01' NAME='This Is Title 1' STOCK='0'>. The only reason you'd put the stock as a separate element is if you can have more than one value per item, which doesn't seem to make sense here.
• Don't ignore your SQL injections, that's a big deal.