The first thing you need to do is upload this XML. Everything is simple:
$xml = simplexml_load_file("data.xml");
Next, decide which columns (attributes) you are interested in. Either you already know the set, or you want to use the ones specified in the columns attribute:
$cols = explode(',', (string)$xml->cards['columns']); // $cols = ['subjectid', 'teacherids']; //если знаете
Next, the XML card nodes need to be translated into an array view. Let us define for this a function that subtracts only the necessary attributes to us:
$map = function($card) use ($cols){ $result = []; foreach($cols as $c){ $result[$c] = (string)$card[$c]; } return $result; };
You have asterisks in the keys there, we will assume that the attributes are string. Otherwise would result in (int) , for example.
Next, go through the elements and build an array of data:
$data = []; foreach($xml->cards->card as $card){ $data[] = $map($card); }
at this stage, we obtain an array of the form
[0] => Array ( [day] => 2 [period] => 1 [subjectid] => *1 [teacherids] => *1 [classroomids] => *3 ) ........
Now you need to connect to the database, use, for example, PDO . You also need SQL query text to insert data. Again, either we know which columns we have and how many values, or not. In the case when we do not know, use something like:
$sql = "insert into xxx (". implode(',', $cols) . ")\n values (". implode(',', array_fill( 0, count($cols), '?')) . ");";
If we know, then we just write a query a la
$sql = "INSERT INTO xxx (subjectid, teacherids) VALUES (?, ?)";
Note that in the general case the column names should be escaped, in the case of mysql these are reverse quotes, for MS SQL, square brackets [] , etc. Or, make sure that the passed rows are valid identifiers of column names and are included in your white list of columns.
At this step, we get the following query text:
insert into xxx (day,period,subjectid,teacherids,classroomids) values (?,?,?,?,?);
Now we prepare the request and execute it for each element of the array:
$pdo = new PDO(...); $st = $pdo->prepare($sql); foreach($data as $d){ $st->execute($d); }
It is worth noting here that for a relatively large amount of data executing a separate query for each insert is a bad practice. It would be better to break the $data array into parts, 300-500 elements and use the variant of the query to insert several lines at once insert ... values (...), (...), (...) .
Yes, in general, bad in any case, but as part of this issue, let's leave it that way.
But in general, if the XML document is large enough, 50 megabytes will already be enough for Simple XML to come down to download the file. In such cases, SAX parsers are used that do not load the entire document into memory.
addition. missed part about values.
generally speaking, we believe that fields are known to us. Odmeplem functions for downloading directories and map mapping.
$loadRefs = function($xml, $refs){ $result = []; foreach($refs as $r ){ foreach($xml->$r->children() as $entry){ $id = (string)$entry['id']; $result[$r][$id] = (string)$entry['name']; } } return $result; }; $map = function($card) use ($refs){ return [ 'day' => (int)$card['day'], 'period' => (int) $card['period'], 'subject' => $refs['subjects'][ (string)$card['subjectid'] ], ]; };
we cause them and we bring result in base, as well as earlier
$refs = $loadRefs($xml, ['classes', 'teachers', 'subjects']); $data = []; foreach($xml->cards->card as $card){ $data[] = $map($card); } $pdo = new PDO(...); $sql = "INSERT INTO xxx (`day`, period, subject) VALUES (?,?,?);"; $st = $pdo->prepare($sql); foreach($data as $d){ $st->execute($d); }