php - XML to MySQL when xml file has multiple matching fields -

i've been doing work on xml mysql using load xml. have been successful itin past. difference latest effort have multiple occurrences of field-name in mysql. sample of below:

<row> <pictures>         <picture name="photo 1">           <filename>image1.jpg</filename>         </picture>         <picture name="photo 2">           <filename>image2.jpg</filename>         </picture>         <picture name="photo 4">           <filename>image3.jpg</filename>         </picture>         <picture name="photo 3">           <filename>image4.jpg</filename>         </picture>         <picture name="photo 7">           <filename>image5.jpg</filename>         </picture>         <picture name="photo 6">           <filename>image6.jpg</filename>         </picture>         <picture name="photo 5">           <filename>image7.jpg</filename>         </picture>         <picture name="photo 8">           <filename>image8.jpg</filename>         </picture>         <picture name="photo 9">           <filename>image9.jpg</filename>         </picture>       </pictures> </row> 

i need import mysql table fields:

• picture1
• picture2
• picture3
• picture4
• picture5
• picture6
• picture7
• picture8
• picture9

as can see, 'name' attribute doesn't occur in correct order, need them inserted in order. first <filename> go picture1, second <filename> picture2 etc..

what being achieved end last <picture> entry in list being in table. assume because filed being overwritten each time.

any ideas how achieve this? have found similar queries no answers yet , have been looking while. rest of file loading fine have unique field-names , can mapped mysql column, struggling one.

as xml not match format aim need transform first. traditionally done xslt can xmlreader , xmlwriter in php has benefit not require keep whole xml document(s) in memory.

the xmlreaderiterator package has support such operations, an example given library.

creating modification of example code taking specific case , exemplary input file named pictures.xml , keeping output standard-output demonstration purposes allows me quote following excerpt:

[... starts examples/read-write.php]   /** @var $iterator xmlwritingiteration|xmlreadernode[] */ $iterator = new xmlwritingiteration($writer, $reader); $writer->startdocument();  $rename = ['row' => 'resultset', 'pictures' => 'row'];  $trimlevel    = null; $picturecount = null;  foreach ($iterator $node) {     $name         = $node->name;     $iselement    = $node->nodetype === xmlreader::element;     $isendelement = $node->nodetype === xmlreader::end_element;     $iswhitespace = $node->nodetype === xmlreader::significant_whitespace;      if (($iselement || $isendelement) && $name === 'filename') {         // drop <filename> opening , closing tags     } elseif ($iselement && $name === 'picture') {         $writer->startelement('field');         $writer->writeattribute('name', sprintf('picture%d', ++$picturecount));         $trimlevel = $node->depth;     } elseif ($trimlevel && $iswhitespace && $node->depth > $trimlevel) {         // drop (trim) significant_whitespace     } elseif ($iselement && isset($rename[$name])) {         $writer->startelement($rename[$name]);         if ($rename[$name] === 'row') {             $picturecount = 0;         }     } else {         $iterator->write();     } } 

this 1 xmlwritingiteration composed of xmlreader , xmlwriter object. iteration allows take on input document (via $iterator->write()) , needed changes on occasions:

• drop <filename> , </filename> tags
• create <field> elements correct name attributes have pictures in document order (mysql xml nomenclature)
• drop significant whitespace <filename> tags dropped well
• rename document element <row> <resultset> (mysql xml nomenclature)
• rename <pictures> element <row> (again mysql xml nomenclature)
• the counter picture fields reset per each (output) row
• everything else kept as-is

such transformation results in following example output xml presented in question:

<?xml version="1.0"?> <resultset>     <row>         <field name="picture1">image1.jpg</field>         <field name="picture2">image2.jpg</field>         <field name="picture3">image3.jpg</field>         <field name="picture4">image4.jpg</field>         <field name="picture5">image5.jpg</field>         <field name="picture6">image6.jpg</field>         <field name="picture7">image7.jpg</field>         <field name="picture8">image8.jpg</field>         <field name="picture9">image9.jpg</field>     </row> </resultset> 

for more information xml format used mysql, please see the mysql documentation --xml commandline switch describes standard xml output format can read in by load xml.

for little example use xslt there no problem whole transformation in memory. if need memory (which can happen if deal xml database dumps), xmlwritingiteration allows iteration based xml transformation xml pull parser (xmlreader) , forward-only xml output via xmlwriter.