Member Avatar for kgizo

Hello I want to send xml values to a database I just created, here is the xml file


<record>
<name>tcpmux</name>
<protocol>tcp</protocol>
<xref type="person" data="Mark_Lottor"/>
<description>TCP Port Service Multiplexer</description>
<number>1</number>
</record>
<record>
<name>tcpmux</name>
<protocol>udp</protocol>
<xref type="person" data="Mark_Lottor"/>
<description>TCP Port Service Multiplexer</description>
<number>1</number>
</record>

I've got 4 columns in my database:- ServiceName, PortNumber, TransportProtocol and Description and I need to send the name, protocol, description and number of each record to my database. I'm new to XML so your help will be highly appreciated.


I was able to print the data onto my screen for viewing.

<?php

$xml = simplexml_load_file('C:\view-source www.iana.org assignments service-names-port-numbers service-names-port-numbers.xml')
or die("Could Not Open The TEXT File<hr /> ");

foreach($xml->children() as $child)
{
foreach($child->children() as $young)
{
echo $young->getName() . ": " . $young . "<br />";
}

echo "<br />";

}

Now all I need is to send the records to MySQL.
Thanks in advance!!!

  • 2 Contributors
  • 1 Reply
  • 231 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Stefano Mtangoo

Dani AI

Generated

Follow-up for (and thanks to for pointing toward PDO): the simplest, reliable pattern is — parse the XML safely, open a PDO connection with a UTF-8 capable charset, prepare a single INSERT once, then loop the XML records and execute the prepared statement inside a transaction. That keeps inserts fast, avoids SQL injection, and preserves Unicode. See the PHP SimpleXML and PDO docs for the APIs used. (php.net)

A compact, practical outline (adjust table/name/credentials as needed):

<?php
libxml_use_internal_errors(true);
if (PHP_VERSION_ID < 80000) libxml_disable_entity_loader(true);

$xml = simplexml_load_file('service-names-port-numbers.xml', 'SimpleXMLElement', LIBXML_NOCDATA);
$pdo = new PDO('mysql:host=127.0.0.1;dbname=yourdb;charset=utf8mb4', 'dbuser', 'dbpass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO services (ServiceName, PortNumber, TransportProtocol, Description)
                       VALUES (:name, :number, :protocol, :description)');

foreach ($xml->record as $r) {
    $stmt->execute([
        ':name' => trim((string)$r->name),
        ':number' => (int)$r->number,
        ':protocol' => trim((string)$r->protocol),
        ':description' => trim((string)$r->description),
    ]);
}
$pdo->commit();

Use the DSN charset (utf8mb4) or call set_charset so MySQL stores 4-byte characters (emoji, some symbols). Also check for parse errors with libxml functions and avoid resolving external entities on older PHP builds. (php.net)

Troubleshooting & tips: validate each element before inserting (use (string)$r->name and isset()), cast ports to (int), trim strings, and handle duplicates with a UNIQUE key plus INSERT ... ON DUPLICATE KEY UPDATE if appropriate. For very large XML files prefer a streaming parser (XMLReader) to avoid high memory use. Wrap long batches in transactions for speed. (php.net)

Security note: prevent XML External Entity (XXE) attacks by disabling DTD/entity resolution and avoid LIBXML_NOENT when parsing untrusted XML — follow the OWASP XXE guidance for parser-specific controls. Confirm parser behavior on the PHP/libxml version in use. (cheatsheetseries.owasp.org)

References: PHP SimpleXML, PDO::prepare, MySQL/PHP charset notes, libxml_disable_entity_loader, and the OWASP XXE cheat sheet. (php.net)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.