Парсинг XML файла средствами MySQL

Question

[Dmirty Kolesnikov]

Здравствуйте, помогите пожалуйста разобраться с импортом данных из XML файла в MySQL. Сам файл довольно большой, и парсить его надо каждые 20 минут. Поэтому по возможности хотелось бы избежать решений, с которыми к файлу пришлось обращаться бы несколько раз для очередного прогона в БД.

Его структура выглядит следующим образом (поменять ее нельзя, это API со стороннего сервиса):

<data>
  <user id="..." login="..." realname="...">
    <items>
      <item_1 img="...">...</item_1>
      <item_2 img="...">...</item_2>
    </items>
  </user>
  <user id="..." login="..." realname="...">
    <items>
      <item_1 img="...">...</item_1>
      <item_2 img="...">...</item_2>
    </items>
  </user>
</data>

Моя таблица в БД создана следующим образом:

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
);

Запрос, который я осуществляю для импорта данных в БД:

LOAD XML INFILE 'file.xml'
REPLACE INTO TABLE user
ROWS IDENTIFIED BY '<user>';

Вопрос обстоит в том, каким образом можно получить данные из item_1 которые находятся в атрибуте img. Когда я создаю в таблице поле с именем «img», туда парсятся данные из атрибута последнего тега item, и дублирования имен полей в MySQL не допустимо, а имена атрибутов поменять нельзя. Если кто знает, как еще можно обратиться к атрибуту через LOAD XML, просьба подсказать.

И еще вопрос, возможно-ли сделать в рамках такого запроса удаление из БД тех записей, id которых не найдены в файле XML?

Спасибо!

Answer 1

[edogs]

поменять ее нельзя, это API со стороннего сервиса

load xml — берет файл с локальной ФС, что мешает (тем или иным образом) обработать хмл (пусть даже в консоли регуляркой вырезав все ненужные item)?
Получили файл от АПИ, обработали, залили в мускул.

возможно-ли сделать в рамках такого запроса удаление из БД тех записей, id которых не найдены в файле XML?

Напрямую никак. Можно импортнуть данные из ХМЛ во временную таблицу и на основе появившихся ИД — снести из основной все лишнее (delete from table main where id not in (select from table tempforxml))

Вопрос обстоит в том, каким образом можно получить данные из item_1 которые находятся в атрибуте img. Когда я создаю в таблице поле с именем «img», туда парсятся данные из атрибута последнего тега item, и дублирования имен полей в MySQL не допустимо, а имена атрибутов поменять нельзя. Если кто знает, как еще можно обратиться к атрибуту через LOAD XML, просьба подсказать.

Напрямую нельзя. Народ извращается с load data infile с последующей работой с xml в базе, по типу такого newtover.tumblr.com/post/14858246616/mysql-load-data-from-xml. Но вряд ли это будет эффективный способ.

Answer 2

[Павел Воронюк]

Вот под Твою XML-ку обработчик на PHP.

Проверяет на уже существующий в базе ID и Login. Если TRUE - не вносит.

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 или Login - существуют!';
	}
}

Answer 3

[Павел Воронюк]

И еще вопрос, возможно-ли сделать в рамках такого запроса удаление из БД тех записей, id которых не найдены в файле XML?

Вот собственно сделай замену этого:

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 или Login - существуют!';
    }

на это:

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 users FROM users WHERE users.id != '$id'";
    	mysql_query($q);
    	header("Location: index.php");
	}

P.S. Извините за спам :) - (сделайте возможность редактировать свой пост :) )

Comments

[Dmirty Kolesnikov]

Спасибо за то, что дали пример, так как после того как я сделал xml2json, исходя из слишком больших потребностей данного метода пришел к simplexml_load_file. У меня вопрос по конструкции else в предложенном варианте для удаления записей присутствующих в БД, но отсутствующих в импортируемом туда XML. В предложенном варианте получается, что действие происходит в случае совпадения результатов, т.е. если данный пользователь найден в БД. Эта проверка актуальна для обновления данных, но для удаления тех данных, которые отсутствуют в импортируемом XML не подходит. Не могли бы вы направить меня в нужную сторону с разбором вопроса удаления неактуальных данных? Может действительно лучше использовать временную таблицу? Спасибо!

[Павел Воронюк]

SELECT-ом мы выбираем записи id и login с БД которые совпадают с xml. Если их нет - мы делаем INSERT, в противном случае (else) - мы удаляем с БД те записи (id) которые не равны ( != ) записям с xml.

[Dmirty Kolesnikov]

Мысль понял, но к сожалению не работает. В случае присутствия записи я поставил просто обновление данных в ней. Но удаление тех записей из БД, которые отсутствуют в XML надо реализовывать как-то иначе. Или же я не правильно понял суть конструкции DELETE users FROM users WHERE users.id != '$id', мне тут не понятно значение "users.id", это какой-то глобальный поиск по всем идентификаторам в таблице?

[Павел Воронюк]

"DELETE users FROM users WHERE users.id != '$id'" В вашем примере должно быть "DELETE user FROM user WHERE user.id != '$id'" user.id - это поле id с таблицы user. $id - это переменная id которую мы вытаскиваем c xml. c БД мы удаляем те id которые НЕ совпадают с id в xml. у меня работает). тут я скорее не корректно написал SELECT удалите - login='$login' OR

[Dmirty Kolesnikov]

Перепроверил, записи не удаляются. Обязательно присутствие header("Location: index.php");? У меня браузер тормозит скрипт из-за циклической переадресации. Да и вызывать PHP я буду из shell, там такой трюк вряд-ли пройдет.

Answer 4

[servekon]

По моему опыту, MySQL+XML очень требовательная к ресурсам связка, с размером файла больше 50 MB даже с LOAD XML начинаются проблемы.
Есть такая штуки например, как xml2json. Таким образом мой совет — XML перевести в другой формат и уже с ним работать.

Comments

[Masterme]

а xml2json не жрёт память? а mysql+json не жрёт память?

Answer 5

[Дмитрий Скогорев]

Смотрите по запросу «mysql xml ExtractValue attribute»
и да будет вам счастье =)

Comments

[Дмитрий Скогорев]

stackoverflow.com/questions/16475926/extract-value-from-xml-in-mysql