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:
我有一个设备将事件日志输出到xml文件我想将该数据输入到mysql数据库。我想使用LOAD XML，但我不确定我是如何尝试搜索论坛的，但我无法对aswer进行罚款。 XML文件看起来像这样：
<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.
正如您所看到的，每个单元格用逗号分隔，每行由;：单元格为：事件编号，用户名，代码，门，组，日期和时间每次有人打开门时，xml文件也会更新，但它只能容纳100行日志。我想要实现的是，这个数据库将每小时更新XML文件中的数据，但只有新数据才有意义。
Can someone please help me with that?
有人可以帮助我吗？
3 个解决方案
#1
1
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.
XML是数据和描述在一起。您的XML没有提供任何描述。
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.
您不能使用XPath来查找任何内容。您必须解析逗号分隔的文本才能使用它。
This is really a .csv file in disguise.
这实际上是伪装的.csv文件。
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.
你说“数据库应该更新XML文件”。我认为这也是错误的。数据库是关于持久性的。他们不应该更新任何东西。
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.
每当有人打开一扇门时，xml文件也会更新，但它只能容纳100行日志。我想要实现的是，这个数据库将每小时更新XML文件中的数据，但只有新数据才有意义。
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.
如果数据库是源，您将查询数据库，并更新.csv / log文件。我认为最简单的事情就是覆盖它，而不是想弄清楚什么是新的，什么不是。
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.
如果日志文件是源文件，则必须读取它，解析它，并使用某个键循环遍历所有记录。如果没有出现密钥，请插入密钥。如果是，请更新它。
#2
0
Follow this step:-->
请按照以下步骤操作： - >
Step by Step:
一步步：
Parse your XML-File and get the Values from it (save them in an Array)
解析XML文件并从中获取值（将它们保存在数组中）
Connect to your MySQL Database
连接到MySQL数据库
Create a PreparedStatement for the Insert
为插入创建PreparedStatement
Loop over your Array and Insert the Values in the Database
循环遍历数组并在数据库中插入值
Close the Database connection
关闭数据库连接
#3
0
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
您可以使用simplexml_load_file（'filename.xml'）函数并将该xml数据转换为一个数组并使用foreach循环检索它并使数据库连接到mysql并使用查询将其插入数据库
#1
1
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.
XML是数据和描述在一起。您的XML没有提供任何描述。
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.
您不能使用XPath来查找任何内容。您必须解析逗号分隔的文本才能使用它。
This is really a .csv file in disguise.
这实际上是伪装的.csv文件。
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.
你说“数据库应该更新XML文件”。我认为这也是错误的。数据库是关于持久性的。他们不应该更新任何东西。
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.
每当有人打开一扇门时，xml文件也会更新，但它只能容纳100行日志。我想要实现的是，这个数据库将每小时更新XML文件中的数据，但只有新数据才有意义。
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.
如果数据库是源，您将查询数据库，并更新.csv / log文件。我认为最简单的事情就是覆盖它，而不是想弄清楚什么是新的，什么不是。
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.
如果日志文件是源文件，则必须读取它，解析它，并使用某个键循环遍历所有记录。如果没有出现密钥，请插入密钥。如果是，请更新它。
#2
0
Follow this step:-->
请按照以下步骤操作： - >
Step by Step:
一步步：
Parse your XML-File and get the Values from it (save them in an Array)
解析XML文件并从中获取值（将它们保存在数组中）
Connect to your MySQL Database
连接到MySQL数据库
Create a PreparedStatement for the Insert
为插入创建PreparedStatement
Loop over your Array and Insert the Values in the Database
循环遍历数组并在数据库中插入值
Close the Database connection
关闭数据库连接
#3
0
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
您可以使用simplexml_load_file（'filename.xml'）函数并将该xml数据转换为一个数组并使用foreach循环检索它并使数据库连接到mysql并使用查询将其插入数据库