Re: LOAD XML LOCAL INFILE having problems with XML with multiple column types.

[Barry Galbraith]

Here's a snippet from my php script.

$file = addslashes($file);

libxml_use_internal_errors(true);
$sxe = simplexml_load_file($file);
if ($sxe === false){ 
$content .= "Failed to read file. It's probably not valid XML.<br>";
   foreach(libxml_get_errors() as $error) {
        $content .= "&nbsp;&nbsp;&nbsp;$error->message<br>";
		unlink($file);
    }
 } else {

$dom_sxe = dom_import_simplexml($sxe);  // Returns a DomElement object

$dom_output = new DOMDocument('1.0');
$dom_output->formatOutput = true;
$dom_sxe = $dom_output->importNode($dom_sxe, true);
$dom_sxe = $dom_output->appendChild($dom_sxe);
$output = "attachment/no_empty.xml";
file_put_contents($output,$dom_output->saveXML($dom_output, LIBXML_NOEMPTYTAG) );

$updated = "";  // used to display a summary of frequency/technology added

$sql = "LOAD XML LOCAL INFILE '" .$doc_root. "/jobs/egcd0196/attachment/no_empty.xml' 
INTO TABLE temp_aa14
 ROWS IDENTIFIED BY '<NetworkDefinition>'"
 ;
 $mysqli->query($sql);

Having uploaded a xml file, the script reads the xml into a php xml structure, the writes it out to a temporary XML file.
The output xml file is in a format that MySQL can successfully parse.

That temp file is then loaded with MYSQL.

Works for me, YMMV.

Good luck,
Barry.