CODEWITHSUNDEEP
phpmysqlxmlforeach
Reknes
Reknes

Reputation: 3

Import XML to MySQL with simplexml

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);

?>

Upvotes: 0

Views: 883

Answers (1)

Parfait
Parfait

Reputation: 107687

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>

Upvotes: 1

Related Questions