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.

Thanks in advance!!!

  • 2 Contributors
  • 2 Replies
  • 1K Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by shashik493

Dani AI

Generated

already parsed and printed the XML and demonstrated iterating nodes and reading attributes. The safe, maintainable next step is to map each XML node to a column and insert with parameterized queries (avoid the old mysql_* functions). Use a proper DB connection with UTF-8, prepared statements, and a transaction so partial imports do not leave the table in an inconsistent state.

Recommended workflow:

  1. Map fields: <name> -> ServiceName, <number> -> PortNumber, <protocol> -> TransportProtocol, <description> -> Description. Cast numbers to integers and trim strings.
  2. Open a PDO (or mysqli) connection with charset utf8mb4 and ERRMODE_EXCEPTION.
  3. Prepare a single INSERT statement and execute it for each record inside a transaction. For very large XML use XMLReader to stream and batch inserts.
  4. Add a UNIQUE index or use ON DUPLICATE KEY UPDATE if duplicates must be avoided. Validate XML first (libxml_use_internal_errors) and handle missing nodes gracefully.

Example (concise) showing the PDO pattern:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8mb4','dbuser','dbpass',
    [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$stmt = $pdo->prepare('INSERT INTO services (ServiceName, PortNumber, TransportProtocol, Description) VALUES (:name,:port,:proto,:desc)');
$xml = simplexml_load_file('services.xml') or die('Cannot open XML');
$pdo->beginTransaction();
foreach ($xml->record as $r) {
  $stmt->execute([
    ':name' => trim((string)$r->name),
    ':port' => intval($r->number),
    ':proto' => trim((string)$r->protocol),
    ':desc' => trim((string)$r->description)
  ]);
}
$pdo->commit();
?>

Troubleshooting notes: confirm file path and well-formed XML, check DB user privileges, catch exceptions with try/catch for logging, and consider XMLReader for memory-heavy imports. If the <xref> attribute needs storing, access it as (string)$record->xref['data'] and validate before inserting.

Member Avatar for kgizo

Sorry I forgot to mention that I was able to print the dat onto my screen for viewing.

<?php

$xml = simplexml_load_file('C:\view-source [url]www.iana.org[/url] 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.

Edited by Nick Evan because: Fixed formatting
Member Avatar for shashik493
INSERT XML TO DB
foreach($xml->business as $row)
{
  $business_type = $row->attributes()->type;
  $id = $row->listingAgent->attributes()->id;
  $name = $row->listingAgent->name;
  $email = $row->listingAgent->email;
  mysql_query('INSERT INTO table…');
}



        this is useful to use.
        shashi
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.