Parsing an XML file means MySQL

Hello, please help to deal with the import data from XML file into MySQL. The file itself is quite large and it is necessary to parse every 20 minutes. So if possible, I would like to avoid solutions that the file had to ask several times for regular run type in the database.

Its structure looks like the following (change it impossible, this API with third-party service):
<data>
 <user id="..." login="..." realname="...">
<items>
 <img item_1="...">...</item_1>
 <item_2 img="...">...</item_2>
</items>
</user>
 <user id="..." login="..." realname="...">
<items>
 <img item_1="...">...</item_1>
 <item_2 img="...">...</item_2>
</items>
</user>
</data>


My table in the DB is created as follows:
CREATE TABLE user (
 id INT PRIMARY KEY NOT NULL,
 login VARCHAR(40) NOT NULL,
 realname VARCHAR(40) NOT NULL,
 item_1 VARCHAR(40) NOT NULL
 item_2 VARCHAR(40) NOT NULL
);


The request that I make to import the data into the database:
LOAD XML INFILE 'file.xml'
REPLACE INTO TABLE user
ROWS IDENTIFIED BY '<user>';
</user>


The question is in how you can retrieve data from item_1 located in the img attribute. When I create the table field with the name "img", there are parsed from the data attribute of the last item tag and the duplication of field names in MySQL are not allowed, and attribute names cannot be changed. If anyone knows how to access the attribute through a LOAD of XML, please suggest.

And another question, is it possible to do it within the framework of such request removal from the database those records whose IDs are not found in the XML file?

Thank you!
October 3rd 19 at 02:19

Related questions

More answers about "Parsing an XML file means MySQL"
5 answers
October 3rd 19 at 02:21
Solution
to change it impossible, this API with third-party service
load the xml takes a file from the local FS that hinders (in one way or another) to treat CML (even in the console of the regular season by cutting all unnecessary item)?
Received the file from the API, processed, poured into the muscle.

is it possible to do it within the framework of such request removal from the database those records whose IDs are not found in the XML file?
Directly in any way. You can importnat data from XML into a temporary table and on the basis of ID appeared to carry all the excess from the primary (delete from main table where id not in (select from table tempforxml))

The question is in how you can retrieve data from item_1 located in the img attribute. When I create the table field with the name "img", there are parsed from the data attribute of the last item tag and the duplication of field names in MySQL are not allowed, and attribute names cannot be changed. If anyone knows how to access the attribute through a LOAD of XML, please suggest.
Directly it is impossible. People with distorted load data infile with follow-up work with xml in the database, type this newtover.tumblr.com/post/14858246616/mysql-load-data-from-xml. But this is unlikely to be effective way.
October 3rd 19 at 02:23
Solution

Here's Your XML-ku handler in PHP.

Checks for an existing database ID and Login. If TRUE - does not make.


mysql_connect("localhost", "xmluploader", "xmluploader") or die(mysql_error());
mysql_select_db("xmluploader") or die(mysql_error()); 

$xmlURL = "xml.xml";

$sxml = simplexml_load_file($xmlURL);

foreach($sxml->user as $user) {

 $id = stripslashes($user->attributes()['id']);
 $login = stripslashes($user->attributes()['login']);
 $realname = stripslashes($user->attributes()['realname']);
 $item_1 = stripslashes($user->items->item_1->attributes()['img']);
 $item_2 = stripslashes($user->items->item_2->attributes()['img']);

 $query = mysql_query("SELECT COUNT(*) FROM users WHERE login='$login' OR id='$id'") or die(mysql_error());
 $user = mysql_fetch_row($query);
 $total = $user[0];

 if ("$total" == 0) {
 $sql = ("INSERT INTO users (id,login, realname, item_1, item_2) VALUES('$id','$login', '$realname', '$item_1', '$item_2')");
 $result = mysql_query($sql) or die("Error ".mysql_error());
 } else {
 echo 'ID or Login exist!';
}
}

October 3rd 19 at 02:25
Solution
And another question, is it possible to do it within the framework of such request removal from the database those records whose IDs are not found in the XML file?

That's actually do replace this:


if ("$total" == 0) {
 $sql = ("INSERT INTO users (id,login, realname, item_1, item_2) VALUES('$id','$login', '$realname', '$item_1', '$item_2')");
 $result = mysql_query($sql) or die("Error ".mysql_error());
 } else {
 echo 'ID or Login exist!';
}

this: 


if ("$total" == 0) {
 $sql = ("INSERT INTO users (id,login, realname, item_1, item_2) VALUES('$id','$login', '$realname', '$item_1', '$item_2')");
 $result = mysql_query($sql) or die("Error ".mysql_error());
 } else {
 $q = "DELETE FROM users users WHERE users.id != '$id'";
mysql_query($q);
 header("Location: index.php");
}

PS Sorry for the spam :) - (please edit your post :) )

Thank you for giving an example, because after I did xml2json, proceeding from too great demands of this method came to simplexml_load_file. I have a question about the design else the proposed option to delete records present in the database, but missing in the imported XML there. In the proposed variant it turns out that the action takes place in the case of coincidence of the results, i.e. if the user was found in database. This check is relevant for updating the data, but to remove those data that do not exist in the imported XML is not suitable. Could you point me in the right direction with the analysis of the question of removal of irrelevant data? Maybe it really is better to use a temporary table? Thank you! - clay.Ha commented on October 3rd 19 at 02:28
SELECT-om, we select the record id and login with the database that match the xml. If they are not - we do INSERT, otherwise (else) - we remove from the database those records (id) not equal ( != ) records in the xml. - neoma_Dooley commented on October 3rd 19 at 02:31
Understood the idea, but unfortunately does not work. In the case of the presence of the record I put just the update of the database. But removing those records from DB that are missing in the XML needs to be implemented otherwise. Or I not correctly understood the essence of the design DELETE users FROM users WHERE users.id != '$id', I don't understand the meaning of "users.id" is a global search for all IDs in the table? - clay.Ha commented on October 3rd 19 at 02:34
"DELETE users FROM users WHERE users.id != '$id'" In your example should be "DELETE FROM user user WHERE user.id != '$id'" user.id is field id from the table user. $id is the variable id which we get c xml. c DB we delete those IDs which do NOT coincide with the id in the xml. works for me). then I would rather not written correctly SELECT delete - login='$login' OR - neoma_Dooley commented on October 3rd 19 at 02:37
Checked entries are not removed. Must be present header("Location: index.php");? My browser slows down the script because of a circular redirect. Yes, and I'll call PHP from the shell, there is a trick hardly pass. - clay.Ha commented on October 3rd 19 at 02:40
October 3rd 19 at 02:27
In my experience, MySQL+XML is a very demanding bunch, with file size more than 50 MB even with LOAD XML problems begin.
There is such a thing for example like xml2json. So my advice is to translate XML to another format and already to work with him.
xml2json and not eating memory? and mysql+json is not eating memory? - clay.Ha commented on October 3rd 19 at 02:30
October 3rd 19 at 02:29
Look at the query "mysql ExtractValue xml attribute"
and Yes you will be happy =)
stackoverflow.com/questions/16475926/extract-value-from-xml-in-mysql - clay.Ha commented on October 3rd 19 at 02:32

Find more questions by tags MySQL