Inserting data from xml file into mysql database

Question

I have a device that outputs event log into a xml file I would like to input that data into a mysql database. I wanted to use LOAD XML but i'm not sure how I tried to search the forum but I couldn't fine the aswer. XML File looks like that:

<response>
<ROW0>:0000,dp0,54321,1,g1,00/00/00-00.00;:0001,dp0,54321,1,g1,00/00/00-00.00;:0002,dp0,54321,1,g1,00/00/00-00.00;:0003,dp0,54321,1,g1,00/00/00-00.00;:0004,dp0,54321,1,g1,00/00/00-00.00;:0005,dp0,54321,1,g1,00/00/00-00.00;:0006,dp0,54321,1,g1,00/00/00-00.00;:0007,dp0,54321,1,g1,00/00/00-00.00;:0008,dp0,54321,1,g1,00/00/00-00.00;:0009,dp0,54321,1,g1,00/00/00-00.00;:0010,dp0,54321,1,g1,00/00/00-00.00;:0011,dp0,54321,1,g1,00/00/00-00.00;:0012,dp0,54321,1,g1,00/00/00-00.00;:0013,dp0,54321,1,g1,00/00/00-00.00;:0014,dp0,54321,1,g1,00/00/00-00.00;:0015,dp0,54321,1,g1,00/00/00-00.00;:0016,dp0,54321,1,g1,00/00/00-00.00;:0017,dp0,54321,1,g1,00/00/00-00.00;:0018,dp0,54321,1,g1,00/00/00-00.00;:0019,dp0,54321,1,g1,00/00/00-00.00;:0020,dp0,54321,1,g1,00/00/00-00.00;:0021,dp0,54321,1,g1,00/00/00-00.00;:0022,dp0,54321,1,g1,00/00/00-00.00;:0023,dp0,54321,1,g1,00/00/00-00.00;:0024,dp0,54321,1,g1,00/00/00-00.00;:0025,dp0,54321,1,g1,00/00/00-00.00;:0026,dp0,54321,1,g1,00/00/00-00.00;:0027,dp0,54321,1,g1,00/00/00-00.00;:0028,dp0,54321,1,g1,00/00/00-00.00;:0029,dp0,54321,1,g1,00/00/00-00.00;:0030,dp2,00106,3,g2,18/06/12-20.02;:0031,dp2,00106,3,g2,18/06/12-20.02;:0032,dp2,00106,3,g2,18/06/12-20.03;:0033,dp2,00106,3,g2,18/06/12-20.04;:0034,dp2,00106,3,g2,18/06/12-23.10;:0035,dp2,00106,3,g2,18/06/12-23.10;:0036,dp2,00106,3,g2,18/06/12-23.10;:0037,dp2,00106,3,g2,18/06/12-23.11;:0038,dp2,00106,3,g2,18/06/12-23.11;:0039,dp2,00106,3,g2,18/06/12-23.15;:0040,dp2,00106,3,g2,18/06/12-23.15;:0041,dp2,00106,3,g2,18/06/12-23.16;:0042,dp2,00106,3,g2,18/06/12-23.16;:0043,dp2,00106,3,g2,18/06/12-23.20;:0044,dp2,00106,3,g2,18/06/12-23.21;:0045,dp2,00106,3,g2,18/06/12-23.21;:0046,dp2,00106,3,g2,18/06/12-23.21;:0047,dp2,00106,3,g2,18/06/12-23.22;:0048,dp2,00106,3,g2,18/06/12-23.22;:0049,dp2,00106,3,g2,18/06/12-23.22;:0050,dp2,00106,3,g2,18/06/12-23.23;:0051,dp2,00106,3,g2,18/06/12-23.23;:0052,dp2,00106,3,g2,18/06/12-23.23;:0053,dp2,00106,3,g2,18/06/12-23.23;:0054,dp2,00106,3,g2,18/06/12-23.24;:0055,dp2,00106,3,g2,18/06/12-23.24;:0056,dp2,00106,3,g2,18/06/12-23.24;:0057,dp2,00106,3,g2,18/06/12-23.24;:0058,dp2,00106,3,g2,18/06/12-23.27;:0059,dp3,00110,3,g2,18/06/12-23.31;:0060,dp3,00110,3,g2,18/06/12-23.31;:0061,dp2,00106,3,g2,18/06/12-23.31;:0062,dp2,00106,3,g2,18/06/12-23.32;:0063,dp2,00106,3,g2,18/06/12-23.34;:0064,dp2,00106,3,g2,18/06/12-23.34;:0065,dp2,00106,3,g2,18/06/12-23.34;:0066,dp2,00106,3,g2,18/06/12-23.34;:0067,dp2,00106,3,g2,18/06/12-23.35;:0068,dp2,00106,3,g2,18/06/12-23.35;:0069,dp2,00106,3,g2,18/06/12-23.36;:0070,dp2,00106,3,g2,18/06/12-23.36;:0071,dp2,00106,3,g2,18/06/12-23.37;:0072,dp2,00106,3,g2,18/06/12-23.38;:0073,dp3,00110,3,g2,18/06/12-23.38;:0074,dp2,00106,3,g2,18/06/12-23.38;:0075,dp2,00106,3,g2,18/06/12-23.39;:0076,dp2,00106,3,g2,18/06/12-23.39;:0077,dp2,00106,3,g2,18/06/12-23.41;:0078,dp3,00110,3,g2,19/06/12-13.52;:0079,dp3,00110,3,g2,19/06/12-13.52;:0080,dp3,00110,3,g2,19/06/12-13.52;:0081,dp3,00110,3,g2,19/06/12-13.52;:0082,dp2,00106,3,g2,19/06/12-15.32;:0083,dp2,00106,3,g2,19/06/12-15.32;:0084,dp3,00110,3,g2,19/06/12-15.32;:0085,dp3,00110,3,g2,19/06/12-15.32;:0086,dp3,00110,3,g2,19/06/12-15.32;:0087,dp3,00110,3,g2,19/06/12-15.33;:0088,dp3,00110,3,g2,19/06/12-15.51;:0089,dp3,00110,3,g2,19/06/12-15.51;:0090,dp3,00110,3,g2,19/06/12-15.52;:0091,dp3,00110,3,g2,19/06/12-15.52;:0092,dp3,00110,3,g2,19/06/12-15.52;:0093,dp3,00110,3,g2,19/06/12-15.53;:0094,dp3,00110,3,g2,19/06/12-15.53;:0095,dp3,00110,3,g2,19/06/12-15.53;:0096,dp3,00110,3,g2,19/06/12-15.53;:0097,dp3,00110,3,g2,19/06/12-15.54;:0098,dp3,00110,3,g2,19/06/12-15.54;:0099,dp3,00110,3,g2,19/06/12-15.54;</ROW0>
</response>

As you can see each cell is separated by comma and each row by ;: The cell are: Event No.,User Name,Code,Door,Group,Date and Time Also that xml file is updated every time that someone opens a door, but it only holds 100 lines of log. What I am trying to achieve is that this database will update data from XML File every hour but only the data that new, if that makes sense.

Can someone please help me with that?

Answer 1

You can use simplexml_load_file('filename.xml') function and take that xml data to one array and retrieve it using foreach loop and make database connection to mysql and using query you can insert it to database

Answer 2

I'm sorry, but nothing about this design makes sense to me.

XML is data and description together. Your XML is providing no description whatsoever. <ROW0> tells me nothing.

Worse, you have a comma-delimited text child with no tags or meta-data.

Why wouldn't you do something like this?

<response>
    <entries>
        <entry>
            <name>0000</name>
            <door>dp0</door>
            <group>54321</group>
            <date>2012-11-30</date>
            <time>11:12:13</time>
        </entry>      
    </entries>
</response> 

You can't use XPath to find anything. You have to parse the comma-delimited text in order to work with it.

This is really a .csv file in disguise.

You said "database should be updating the XML file". I think that's wrong-headed, too. Databases are about persistence. They shouldn't be updating anything.

Also that xml file is updated every time that someone opens a door, but it only holds 100 lines of log. What I am trying to achieve is that this database will update data from XML File every hour but only the data that new, if that makes sense.

This sounds like you want a scheduled job that will wake up hourly and do something. I'm having trouble telling which is the source and which is the final resting place - the log file or the database.

If the database is the source, you'll query your database, and update the .csv/log file. I think the simplest thing to do is to overwrite it and not be fancy about figuring out what's new and what's not.

If the log file is the source, you'll have to read it, parse it, and loop over all the records using some key. If the key does not appear, INSERT it. If it does, UPDATE it.

Answer 3

Follow this step:-->

Step by Step:

Parse your XML-File and get the Values from it (save them in an Array)

Connect to your MySQL Database

Create a PreparedStatement for the Insert

Loop over your Array and Insert the Values in the Database

Close the Database connection