Validate xml file before updating database

Question

I have a file upload system that when sending the xml file it sends the insertions and updates in the database, but if a file with the different structure is sent I get errors. I have already found some solutions using DTD and xml schemas, but still I am a little confused since the examples do not use the same file structure as I get.

How can I ensure that the uploaded file really has the desired structure using php and mysql?

Following is an example of the xml file:

<?xml version="1.0"?>
<table>
    <row>
        <column>
            131290444
        </column>
        <column>
            Nome Completo
        </column>
        <column>
            10/09/1991
        </column>
        <column>
            39077161830
        </column>
        <column>
            [email protected]
        </column>
        <column>
            Aluno Regularmente Matriculado
        </column>
    </row>
    <row>
        <column>
            151290202
        </column>
        <column>
            Nome completo 2
        </column>
        <column>
            20/09/1987
        </column>
        <column>
            37999131814
        </column>
        <column>
            [email protected]
        </column>
        <column>
            Aluno Regularmente Matriculado
        </column>
    </row>
</table>

Answer 1

To work with XML in PHP I like to use the Xmlreader and Xmlwriter classes because they process node by node instead of loading everything in memory like other XML manipulation classes with PHP.

Without using the DTD or XML Schema you can use Xmlreader to find the nodes and compare with a pattern you want.

Example - check if each Row has 6 columns and check the data for each column

<?php
$error = array();
$xml_file = 'arquivo.xml';
$reader = new XMLReader();
$reader->open($xml_file);
$r = 0;
while ($reader->read()){//enquanto tiver nós no xml
  if ($reader->nodeType == XMLREADER::ELEMENT && $reader->name === 'row'){ //acha um row
    $r++;
    $row = new XMLReader; //nova instância para ler o xml dentro do nó row
    $r = $reader->readOuterXML(); //pega todo o nó
    $row->xml($r); //e carrega o nó na nova instância xml
    $c = 0; 
     while ($row->read()){//enquanto tiver nós no row  
        if ($row->nodeType == XMLREADER::ELEMENT && $reader->name !== 'column'){//verifica se o nó é diferente de <column>
        $c++;
             $error[] = 'Nó ' . $c . ' do row ' . $r . ' diferente de column';
        }
        if ($row->nodeType == XMLREADER::ELEMENT && $reader->name !== 'column'){
        //encontramos uma coluna
        $c++;
            $conteudo = $row->readInnerXML(); //pega o conteúdo do nó
            //aqui você pode testar cada tipo de dado de acordo com o número do nó
        }
     }//acabaram os nós do row    
    if ($c !== 6) {
        $error[] = 'Row '.$r.'tem um total de '.$c.' colunas.';//se não tiver 6 colunas
  }
}//acabaram as rows
var_dump ($error); //dump dos erros 
?>

That’s just an example to start with, I didn’t test it. But this way you can process Xmls of any size without consuming too many resources from your server. Check Xmlreader manual for more options of functions.

Answer 2

You can simply read your file and check the amount of columns for each item in your XML:

$xmlFile = <<<EOF
<?xml version="1.0"?>
<table>
    <row>
        <column>
            131290444
        </column>
        <column>
            Nome Completo
        </column>
        <column>
            10/09/1991
        </column>
        <column>
            39077161830
        </column>
        <column>
            [email protected]
        </column>
        <column>
            Aluno Regularmente Matriculado
        </column>
    </row>
    <row>
        <column>
            151290202
        </column>
        <column>
            Nome completo 2
        </column>
        <column>
            20/09/1987
        </column>
        <column>
            37999131814
        </column>
        <column>
            [email protected]
        </column>
        <column>
            Aluno Regularmente Matriculado
        </column>
    </row>
</table>
EOF;

function verifyRolesInXMLFile($urlFile) {

    try {

           //lê o arquivo XML
             $xml = new SimpleXMLElement($urlFile);

           if (count($xml->row) != 2) {
              throw new Exception("Número de <rows> tem que ser 2, o total deu: ".count($xml->row));
           } 

           if (count($xml->row[0]->column) != 6) {
              throw new Exception("Número de <column> da primeira linha tem que ser 6, o total deu: ".count($xml->row[0]->column));
           } 

           if (count($xml->row[1]->column) != 6) {
              throw new Exception("Número de <column> da linha de baixo tem que ser 6, o total deu: ".count($xml->row[1]->column));
           } 
          echo $xml;
       } catch (\Exception $e) {
           echo $e->getMessage();
       }
}
//executa o método de verificação de colunas
verifyRolesInXMLFile($xmlFile);

See the solution working here