Import XML to MySQL with simplexml

Question

I have tried to use some scripts from this page to use for my webshop. I want to import an XML-file to my MySQL database. I got the script working, but only the first orderline is imported, not the second.

This is an example of my XML code:

<?xml version="1.0" encoding="UTF-8"?>
<eWebBooking>
   <OrderHead>
      <Action>SendFromWarehouse</Action>
      <OrderReference>5986-20193315</OrderReference>
      <EarliestOrderDate/>
      <OrderNumber>20193315</OrderNumber>
      <SalesOrg>Web</SalesOrg>
      <DistChannel>Parcel</DistChannel>
      <InvoiceReference>Reknes</InvoiceReference>
      <Orderer>
         <Name>Ola Nordamann</Name>
         <Telephone>22225555</Telephone>
         <Email/>
         <StreetName>Hackerstreet 46</StreetName>
         <PostalCode>5986</PostalCode>
         <Region>LEGOLAND</Region>
      </Orderer>
   </OrderHead>


   <OrderLines>
      <OrderLine>1</OrderLine>
      <ArticleNo>10501</ArticleNo>
      <ArticleDescription>MPOW Dunmer Earphone Blueth</ArticleDescription>
      <ArticleBrand>MPOW</ArticleBrand>
      <DlvDate>2016-09-09</DlvDate>
      <NoOfPieces>1</NoOfPieces>
      <DeliveryStore>eWeb</DeliveryStore>
      <DeliveryCode>DELIVERY</DeliveryCode>
      <Weight>0,2</Weight>
      <Volume>70</Volume>
      <ShipmentNo>73600698093661246</ShipmentNo>
      <ParcelNo>373600596034663364</ParcelNo>
   </OrderLines>
   <OrderLines>
      <OrderLine>2</OrderLine>
      <ArticleNo>10528</ArticleNo>
      <ArticleDescription>Mpow MBS5 Armor Bluetooth</ArticleDescription>
      <ArticleBrand>MPOW</ArticleBrand>
      <DlvDate>2016-09-07</DlvDate>
      <NoOfPieces>1</NoOfPieces>
      <DeliveryStore>eWeb</DeliveryStore>
      <DeliveryCode>PICKUP</DeliveryCode>
      <Weight>4</Weight>
      <Volume>50</Volume>
      <ShipmentNo>73600698093661246</ShipmentNo>
      <ParcelNo>00373600698636066394</ParcelNo>
    </OrderLines>
</eWebBooking>

I guess that i have to change some of the foreach code?
Here is a copy of my PHP file/code:

<?php

ini_set('display_errors','On');

$con2 = mysql_connect("databasehost","databaseuser","databasepass");
if (!$con2)  {  
    die('Could not connect: ' . mysql_error());  
}

$selectdb = mysql_select_db("databasename", $con2);
if (!$selectdb)  { 
    die('Database not used: ; ' . mysql_error());  
}

$file_arr = array();

if ($handle = opendir('.')) {
    while (false !== ($file = readdir($handle))) {
        if (($file != ".") && ($file != "..")) {
            if(substr($file, -4) == ".xml")
            {
                array_push($file_arr, $file);
            }
        }
    }
    closedir($handle);
}

    foreach($file_arr as $filename)

{
     $xml = simplexml_load_file($filename);

    $Action = mysql_real_escape_string($xml->OrderHead->Action);
    $OrderReference = mysql_real_escape_string($xml->OrderHead->OrderReference);    
    $EarliestOrderDate = mysql_real_escape_string($xml->OrderHead->EarliestOrderDate);
    $OrderNumber = mysql_real_escape_string($xml->OrderHead->OrderNumber);
    $SalesOrg = mysql_real_escape_string($xml->OrderHead->SalesOrg);
    $DistChannel = mysql_real_escape_string($xml->OrderHead->DistChannel);
    $InvoiceReference = mysql_real_escape_string($xml->OrderHead->InvoiceRefernce);
    $Name = mysql_real_escape_string($xml->OrderHead->Orderer->Name);
    $Telephone = mysql_real_escape_string($xml->OrderHead->Orderer->Telephone);
    $Email = mysql_real_escape_string($xml->OrderHead->Orderer->Email);
    $StreetName = mysql_real_escape_string($xml->OrderHead->Orderer->StreetName);
    $PostalCode = mysql_real_escape_string($xml->OrderHead->Orderer->PostalCode);
    $Region = mysql_real_escape_string($xml->OrderHead->Orderer->Region);


    $OrderLine = mysql_real_escape_string($xml->OrderLines->OrderLine);
    $ArticleNo = mysql_real_escape_string($xml->OrderLines->ArticleNo);
    $ArticleDescription = mysql_real_escape_string($xml->OrderLines->ArticleDescription);
    $ArticleBrand = mysql_real_escape_string($xml->OrderLines->ArticleBrand);
    $DlvDate = mysql_real_escape_string($xml->OrderLines->DlvDate);
    $NoOfPieces = mysql_real_escape_string($xml->OrderLines->NoOfPieces);
    $DeliveryStore = mysql_real_escape_string($xml->OrderLines->DeliveryStore);
    $DeliveryCode = mysql_real_escape_string($xml->OrderLines->DeliveryCode);
    $Weight = mysql_real_escape_string($xml->OrderLines->Weight);
    $Volume = mysql_real_escape_string($xml->OrderLines->Volume);
    $ShipmentNo = mysql_real_escape_string($xml->OrderLines->ShipmentNo);
    $ParcelNo = mysql_real_escape_string($xml->OrderLines->ParcelNo);
    $TimeWindowStart = mysql_real_escape_string($xml->OrderLines->TimeWindowStart);
    $TimeWindowEnd = mysql_real_escape_string($xml->OrderLines->TimeWindowEnd);


    mysql_query("INSERT INTO xml (Action, OrderReference, EarliestOrderDate, OrderNumber, SalesOrg, DistChannel, InvoiceReference, Name, Telephone, Email, StreetName, PostalCode, Region, OrderLine, ArticleNo, ArticleDescription, ArticleBrand, DlvDate, NoOfPieces, DeliveryStore, DeliveryCode, Weight, Volume, ShipmentNo, ParcelNo, TimeWindowStart, TimeWindowEnd)
    VALUES ('$Action', '$OrderReference', '$EarliestOrderDate', '$OrderNumber', '$SalesOrg', '$DistChannel', '$InvoiceReference', '$Name', '$Telephone', '$Email', '$StreetName', '$PostalCode', '$Region', '$OrderLine', '$ArticleNo', '$ArticleDescription', '$ArticleBrand', '$DlvDate', '$NoOfPieces', '$DeliveryStore', '$DeliveryCode', '$Weight', '$Volume', '$ShipmentNo', '$ParcelNo', '$TimeWindowStart', '$TimeWindowEnd')")
    or die(mysql_error());


    printf ("Records inserted: %d\n", mysql_affected_rows());  
    echo " <p />--- --- --- --- --- --- --- --- ---<p />";
}

mysql_close($con2);

?>

Please stop using the `mysql_*` functions. They were deprecated in PHP 5.5, which is so old it no longer even receives security updates, and completely removed in PHP 7. Instead, use `mysqli_*` or PDO. See https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — ChrisGPT was on strike, Sep 03 '16 at 18:23

score 1 · Accepted Answer · answered Sep 03 '16 at 21:30

Consider MySQL's LOAD XML LOCAL INFILE for bulk import of XML document. However, to use this command, your XML must be simplified to align to database fields and values. And to restructure original document for such an import, consider XSLT (the XML transformation language used to manipulate source documents). XSLT can transform original XML to a simpler, flatter structure for database upload. PHP maintains an XSLT 1.0 processor using the libxslt engine.

XSLT Script (save as .xsl file to be read in PHP below)

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

  <xsl:template match="/eWebBooking">
     <xsl:copy>
        <xsl:apply-templates select="OrderLines"/>
     </xsl:copy>
  </xsl:template>

  <xsl:template match="OrderLines"> 
   <xsl:copy>          
       <xsl:copy-of select="ancestor::eWebBooking/OrderHead/*[not(local-name()='Orderer')]"/>
       <xsl:copy-of select="ancestor::eWebBooking/OrderHead/Orderer/*"/>
       <xsl:copy-of select="*"/>
   </xsl:copy>
  </xsl:template>    
</xsl:transform>

PHP Script (below uses PDO to connect to MySQL; you may have to enable php_xsl extension and set -local-infile in MYSQL)

$file_arr = array();

if ($handle = opendir('.')) {
    while (false !== ($file = readdir($handle))) {
        if (($file != ".") && ($file != "..")) {
            if(substr($file, -4) == ".xml")
            {
                array_push($file_arr, $file);
            }
        }
    }
    closedir($handle);
}

foreach($file_arr as $filename) {

     // LOAD XML AND XSL SOURCES
     $doc = simplexml_load_file($filename);
     $xsl = simplexml_load_file('XSLTScript.xsl');

     // TRANSFORM SOURC
     $proc = new XSLTProcessor;
     $proc->importStyleSheet($xsl); 
     $newDoc = $proc->transformToXML($doc);

     // SAVE OUTPUT TO FILE         
     $xmlfile = 'temp.xml';
     file_put_contents($xmlfile, $newDoc);

     // RUN MYSQL COMMAND 
     try {
        $db = new PDO('mysql:host=databasehost;dbname=databasename', 
                      $databaseuser, $databasepwd); 
        $db->query("LOAD XML DATA INFILE 'path/to/temp.xml'
                    INTO TABLE xml
                    ROWS IDENTIFIED BY '<OrderLines>';");
     } catch(PDOException $e) {  
            echo $e->getMessage(); 
     } 
}

Transformed XML (to be imported in db, all element names must match database names)

<?xml version="1.0" encoding="UTF-8"?>
<eWebBooking>
  <OrderLines>
    <Action>SendFromWarehouse</Action>
    <OrderReference>5986-20193315</OrderReference>
    <EarliestOrderDate/>
    <OrderNumber>20193315</OrderNumber>
    <SalesOrg>Web</SalesOrg>
    <DistChannel>Parcel</DistChannel>
    <InvoiceReference>Reknes</InvoiceReference>
    <Name>Ola Nordamann</Name>
    <Telephone>22225555</Telephone>
    <Email/>
    <StreetName>Hackerstreet 46</StreetName>
    <PostalCode>5986</PostalCode>
    <Region>LEGOLAND</Region>
    <OrderLine>1</OrderLine>
    <ArticleNo>10501</ArticleNo>
    <ArticleDescription>MPOW Dunmer Earphone Blueth</ArticleDescription>
    <ArticleBrand>MPOW</ArticleBrand>
    <DlvDate>2016-09-09</DlvDate>
    <NoOfPieces>1</NoOfPieces>
    <DeliveryStore>eWeb</DeliveryStore>
    <DeliveryCode>DELIVERY</DeliveryCode>
    <Weight>0,2</Weight>
    <Volume>70</Volume>
    <ShipmentNo>73600698093661246</ShipmentNo>
    <ParcelNo>373600596034663364</ParcelNo>
  </OrderLines>
  <OrderLines>
    <Action>SendFromWarehouse</Action>
    <OrderReference>5986-20193315</OrderReference>
    <EarliestOrderDate/>
    <OrderNumber>20193315</OrderNumber>
    <SalesOrg>Web</SalesOrg>
    <DistChannel>Parcel</DistChannel>
    <InvoiceReference>Reknes</InvoiceReference>
    <Name>Ola Nordamann</Name>
    <Telephone>22225555</Telephone>
    <Email/>
    <StreetName>Hackerstreet 46</StreetName>
    <PostalCode>5986</PostalCode>
    <Region>LEGOLAND</Region>
    <OrderLine>2</OrderLine>
    <ArticleNo>10528</ArticleNo>
    <ArticleDescription>Mpow MBS5 Armor Bluetooth</ArticleDescription>
    <ArticleBrand>MPOW</ArticleBrand>
    <DlvDate>2016-09-07</DlvDate>
    <NoOfPieces>1</NoOfPieces>
    <DeliveryStore>eWeb</DeliveryStore>
    <DeliveryCode>PICKUP</DeliveryCode>
    <Weight>4</Weight>
    <Volume>50</Volume>
    <ShipmentNo>73600698093661246</ShipmentNo>
    <ParcelNo>00373600698636066394</ParcelNo>
  </OrderLines>
</eWebBooking>

@IanBrindley - Awesome! Even better when solutions help more than the OP, especially future readers. — Parfait, Sep 04 '16 at 14:03
It solved my problem, but it will not input data to my database when running the MYSQL COMMAND. — Reknes, Sep 04 '16 at 20:26
What is the exception/error you receive? As mentioned, do the element names exactly match db table field names? Use one of the loop's temp.xml outputs and run this command outside PHP like your MySQL console. — Parfait, Sep 04 '16 at 20:31

Import XML to MySQL with simplexml

1 Answers1