How to export data to the database from an updated XML file?

Question

SQL

There is a periodically updated xml file, for example containing information about real estate objects. The data from this file is loaded into the MySQL database.
If this is done once, everything is clear: we parse XML (I use PHP), then INSERT into the table.
But what if the XML file has been updated (some elements have been deleted, the contents of others have changed) and the data in the table needs to be updated accordingly?
Only TRUNCATE tables and INSERT on new came to mind. Maybe you can somehow track specific changes in the XML file and do an UPDATE accordingly?

Answer 1

If there are no problems with the performance of TRUNCATE,INSERT, then you shouldn't even bother.
If the amount of data is large, but they change little, then the best option would be to add the “version”, “time stamp”, etc. fields to the xml, so that it would be as easy as possible to track changes. It would also be worthwhile to store a list of deleted records in a separate file.
If you really don't want to do a TRUNCATE, write an INSERT_OR_UPDATE stored procedure that will either add a record or update it. Firebird, for example, can INSERT OR UPDATE out of the box.
If you do not keep a log of deleted records, then the task will still be reduced to checking "If the record is not in xml, then delete it from the database" for all records in the database.