Import XML in PHP to add to Mysql DB

Question

I've been trying to crack this one and just haven't been able to.

Basically I have an XML file that contains train line timetable information, and it is really complex.

I need to capture every bit of information and add to a MySQL DB, but as you'll see the parent/child and key/pair is quite dynamic and sometimes it has more or less stuff.

I have been looking a SimpleXML, but cannot get the values out of the XML, in a way it is flexible to the change in information/data.

Please note that from the 1st Journey to the second, there new fields, which as you probably guessed, it needs to captured.

How can get the elements inside the XLM below:

And also for each of the elements like:

The OR is really important to capture as also the other values.

Many thanks

Lucio

Experimental PHP code:

<?php
$xml = simplexml_load_file($argv[1]) or die("Error: Cannot create object");
//
//  TimeTableID
//
$timetableId = $xml->attributes()->timetableID;
echo "Timetable id: " . $timetableId . "\n\r";
//
// Journey
// I've added the atributes->
foreach ($xml->Journey as $Journey) {
    echo "Journey data: " . $Journey["rid"] . "\n\r";
    echo "Journey data: " . $Journey["uid"] . "\n\r";
    echo "Journey data: " . $Journey["trainId"] . "\n\r";
    echo "Journey data: " . $Journey["ssd"] . "\n\r";
    echo "Journey data: " . $Journey["toc"] . "\n\r";
}
?>

XML file extract:

<PportTimetable timetableID="20180124020740">
<Journey rid="201801247176474" uid="G76474" trainId="2H98" ssd="2018-01-24" toc="SN">     
    <OR tpl="BCKNMJC" act="TB" ptd="07:15" wtd="07:15" />
    <IP tpl="BIRKBCK" act="T " pta="07:18" ptd="07:18" wta="07:17:30" wtd="07:18" />
    <PP tpl="CRYSBRJ" wtp="07:20" />
    <IP tpl="CRYSTLP" act="T " plat="1" pta="07:22" ptd="07:22" wta="07:21:30" wtd="07:22" />
    <IP tpl="GIPSYH" act="T " pta="07:25" ptd="07:25" wta="07:24:30" wtd="07:25" />
    <IP tpl="WNORWOD" act="T " plat="1" pta="07:28" ptd="07:28" wta="07:27:30" wtd="07:28" />
    <PP tpl="WNORWDJ" wtp="07:29" />
    <IP tpl="TULSEH" act="T " plat="3" pta="07:31" ptd="07:34" wta="07:31" wtd="07:34" />
    <IP tpl="NDULWCH" act="T " pta="07:37" ptd="07:37" wta="07:36:30" wtd="07:37:30" />
    <IP tpl="EDULWCH" act="T " pta="07:39" ptd="07:40" wta="07:39" wtd="07:40" />
    <IP tpl="PKHMRYC" act="T " plat="2" pta="07:42" ptd="07:43" wta="07:42" wtd="07:43" />
    <IP tpl="PCKHMQD" act="T " plat="1" pta="07:45" ptd="07:45" wta="07:45" wtd="07:45:30" />
    <PP tpl="OLDKRDJ" wtp="07:46" />
    <DT tpl="SBRMNDS" act="TF" pta="07:52" wta="07:52" />
  </Journey>

  <Journey rid="201801248980806" uid="Y80806" trainId="5G45" ssd="2018-01-24" toc="VT" trainCat="EE" isPassengerSvc="false">
    <OPOR tpl="WVRMPTN" act="TB" plat="1" wtd="22:40" />
    <PP tpl="WVRMTNJ" wtp="22:41:30" />
    <PP tpl="WVRMSRJ" wtp="22:44" />
    <OPDT tpl="OXLEYCS" act="TF" wta="22:47" />
  </Journey>
</PportTimetable>

You need to include your PHP code as well if this is tagged PHP. — tadman
– tadman, Commented Jan 29, 2018 at 21:42

The fourth bird · Accepted Answer · 2018-01-30 11:14:20Z

0

You could loop the children of the SimpleXMLElement:

foreach ($Journey->children() as $child) {
    if ($child->getName() === "IP") {
        echo $child['tpl'];
    }
}

Example output php

answered Jan 30, 2018 at 11:14

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lucio · Accepted Answer · 2018-01-31 21:35:57Z

Thank you for your contribution.I moved away from SimpleXML to xmlReader.

This is how I addressed my own question, might not be slick but it is working:

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$xmlReader = new XMLReader();
// open the file for reading
$xmlReader->open($argv[1]);

// Inittiate var
$rid = "";
$sql_table_name = "";
$number_of_inserts = 0;
    while ($xmlReader->read()) { 
        // check to ensure nodeType is an Element not attribute or #Text  
        if ($xmlReader->nodeType == XMLReader::ELEMENT) {

            if ($xmlReader->hasAttributes) {

                //Reset values to NULL
                $name_values = "";
                $values = "";

                $attribute_count = $xmlReader->attributeCount;
                $sql_counter = 1;
                $sql_table_name = $xmlReader->localName;

                while ($xmlReader->moveToNextAttribute()) {

                    if ($xmlReader->localName == "rid") {
                        $rid = "\"" . $xmlReader->value . "\"";
                    }

                    if ($sql_counter != $attribute_count) {
                        $name_values .= $xmlReader->name . ", ";
                        $values .= "\"" . $xmlReader->value . "\", ";
                    } else {
                        $name_values .= $xmlReader->name;
                        $values .= "\"" . $xmlReader->value . "\"";
                    }

                    $sql_counter++;
                }

                switch ($sql_table_name) {
                    case "Journey":
                        //echo "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $sql = "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $conn->query($sql);
                        $sql = "";
                        $number_of_inserts++;
                        break;

                    case "Association":
                        // echo "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $sql = "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $conn->query($sql);
                        $sql = "";
                        $number_of_inserts++;
                        break;

                    case "main":
                        // echo "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $sql = "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $conn->query($sql);
                        $sql = "";
                        $number_of_inserts++;
                        break;

                    case "assoc":
                        // echo "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $sql = "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $conn->query($sql);
                        $sql = "";
                        $number_of_inserts++;
                        break;

                    default:
                        $name_values = "rid, " . $name_values;
                        $values = $rid . ", " . $values;

                        if ($sql_table_name == "OR") {
                            $sql_table_name = "`" . $sql_table_name . "`";
                        }
                        // echo "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $sql = "INSERT INTO " . $sql_table_name . " (" . $name_values . ") VALUES (" . $values . ");" . "\n\r";
                        $conn->query($sql);
                        $sql = "";
                        $number_of_inserts++;
                }
            }

        }

    }
    $conn->close();

Parfait · Accepted Answer · 2018-01-31 22:34:50Z

Consider MySQL's LOAD XML facility which can import XML data directly into database analogous to LOAD CSV. However, you need to flatten and simplify XML to fit the doc specifications. Fortunately, a great tool is available to transform such an attribute-centric XML: XSLT, the special-purpose langauge designed to transform XML files for end use needs. PHP can run XSLT 1.0 scripts with its php-xsl class.

Specifically XSLT below extracts all <Journey> attributes. You can change Journey in XSLT to the others: Association, main, etc. if they reside as children nodes to root. Of course, be sure your MySQL destination table has all possible fields from XML.

XSLT (save as .xsl file)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:template match="/PportTimetable">
    <data>
      <xsl:apply-templates select="Journey"/>
    </data>
  </xsl:template>

  <xsl:template match="Journey">
    <row>
        <xsl:apply-templates select="@*"/>
    </row>
  </xsl:template>

  <xsl:template match="Journey/@*">
    <xsl:element name="{name(.)}"><xsl:value-of select="."/></xsl:element>  
  </xsl:template>

</xsl:stylesheet>

PHP (no loops or if statements)

// LOAD XML AND XSL SOURCES
$doc = new DOMDocument();
$doc->load('Input.xml');

$xsl = new DOMDocument;
$xsl->load('XSLT_Script.xsl');

// CONFIGURE TRANSFORMER
$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl); 

// TRANSFORM SOURCE
$newXml = $proc->transformToXML($doc);

// SAVE TO FILE
file_put_contents('Output.xml', $newXml);

// RUN MYSQL COMMAND (MAY NEED TO ALLOW --local-infile IN SETTINGS)
try {
   $conn = new mysqli($servername, $username, $password, $dbname);
   $conn->query("LOAD XML DATA INFILE 'path/to/Output.xml'
                 INTO TABLE Journey
                 ROWS IDENTIFIED BY '<row>';");
} catch(Exception $e) {  
    echo $e->getMessage();  
}

$conn->close();

XML (transformed output passed into LOAD XML)

<?xml version="1.0" encoding="utf-8"?>
<data>
  <row>
    <rid>201801247176474</rid>
    <uid>G76474</uid>
    <trainId>2H98</trainId>
    <ssd>2018-01-24</ssd>
    <toc>SN</toc>
  </row>
  <row>
    <rid>201801248980806</rid>
    <uid>Y80806</uid>
    <trainId>5G45</trainId>
    <ssd>2018-01-24</ssd>
    <toc>VT</toc>
    <trainCat>EE</trainCat>
    <isPassengerSvc>false</isPassengerSvc>
  </row>
</data>

Collectives™ on Stack Overflow

Import XML in PHP to add to Mysql DB

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related