XML Import into MySQL Database

General information for the business: Web services company
Database management system (DBMS): MySQL
Description of requirements/functionality: You will be taking a flat XML File and automatically importing into a MySQL database, into several tables. The import should take care of updating the database if the record exists or adding if it does not already exist. Verification and validation will be required to ensure the integrity of the Db.

If the record is NOT added OR updated it will need to be flagged for deletion (we will take care of cleaning out the records marked for deletion).

The XML file will have records identified with a tag of Property - each property will be subordinate to a record Branch.

The XML file is just ONE file that will be refreshed every hour - you do not need to worry about this process - your solution will just import the file into the MySQL database.

The structure of the XML file has been supplied in the original specification HERE. The key thing is that the XML is one flat XML file, but this will need to be split into different tables based on the XML structure. Please look at the bit of the XML file that shows "ROOMS". This is part of the "PROPERTY" record. Because there are 1 or more rooms it makes sense to put these records into separate tables. Again IF you are a developer for DB's this will make sense and you will understand what is required jut by looking at the XML sample file structure.

IF you look at the XML file you will find a tag/field called "property" that is nestled under "branch". So for each branch there will be potentially 100's of houses (properties). Each property has several fields in the xml file. Some of the fields (such as rooms) will need to be added to its own table, as rooms have multiple properties (YOU need to study the XML file to understand the format).

The format of the XML will always be the same.

So your solution will import file as per original specification.

BE SURE YOU UNDERSTAND WHAT IS REQUIRED OR ASK ME. PLEASE READ THIS SPECIFICATION CAREFULLY AS I AM GETTING SOME QUESTIONS THAT COULD EASILY BE ANSWERED BY READY THIS AND STUDYING THE XML FILE ATTACHED.

So the structure of the XML is:

BRANCH ID=1
PROPERTY ID=xyz
Property fields to be imported
PROPERTY ID=xyz1
Property fields to be imported
PROPERTY ID=xyz12
Property fields to be imported
BRANCH ID=2
PROPERTY ID=xyz34
Property fields to be imported
PROPERTY ID=xyz56
Property fields to be imported
PROPERTY ID=xyz89
Property fields to be imported
etc
etc

(See the sample XML files attached for details)

So for each BRANCH there will be 100's of PROPERTY (Houses) records.

Your solution needs to check the "branchID/propertyID" and apply the logic below if the record exists already in the database. This should be very easy for you to understand if you are a developer, it's called database relational logic. E.g. check if BranchID = 1 and PropertyID=xyz exists already in the table. If it does follow the logic:

If the record is NOT "added" OR "updated" it will need to be "flagged for deletion" (we will take care of cleaning out the records marked for deletion).

This routine will have to be run every hour probably via a Server CRON job and a report emailed to a specified user with records:

* Updated
* Added
* Flagged for deletion (See above to understand why a record would be flagged for deletion)
* Time taken to complete
* Any errors (completion status)

The XML file is a flat file that contains 100's of records. Some of the information will need to be split into multiple MySql tables to create a relational db solution.
Specific technologies required: XML, MySQL, PHP
Extra notes: Sample XML record attached. This will be part of a larger XML file. To be extracted and imported into MySQL via PHP and a CRON job running hourly on the web server. As per requirements above.