Nếu muốn thêm các dữ liệu XML vào database MySQL, ta có thể sử dụng một vài phương pháp tùy thuộc theo mục đích và dữ liệu. Trong bài này, sẽ giới thiệu về phương pháp sử dụng XPath trong MySQL để thêm các dữ liệu RSS vào bảng dữ liệu tương ứng.

Trong PHP, bạn có thể sử dụng SimpleXML để tạo các đối tượng DOM của dữ liệu XML cho những trường hợp cần thao tác nhiều với dữ liệu. Bạn có thể định dạng, tìm kiếm, lọc,… và kết nối với DB để thêm từng dòng vào. Một cách hiệu quả hơn là tạo một biến batch SQL (chứa nhiều câu lệnh SQL) và thực thi một lượt. Nhưng việc tạo ra các đối tượng và vòng lặp sẽ không cần thiết nếu như dữ liệu không đòi hỏi phải can thiệp nhiều. Đây là trường hợp “Thủ công”.

Trường hợp “Hoàn hảo”, dữ liệu XML có định dạng gần như giống hệt với bảng dữ liệu trong DB, bạn chỉ cần dùng cú pháp LOAD XML. Rất đơn giản và tốc độ import cũng rất nhanh, không phải cần phải đọc nội dung XML vào biến, chỉ cần truyền tên tập tin xuống cho MySQL xử lý.

Trường hợp “Tương đối” nằm giữa hai loại trên là trường hợp mà tôi sẽ đề cập chính trong bài viết này. Với trường hợp này, bạn cần trích xuất dữ liệu từ những nội dung hay thuộc tính của các thẻ, tuy nhiên không cần phải kiểm tra, xử lý nhiều. Như đã giới thiệu, tôi sẽ sử dụng cú pháp XPath (Nếu bạn cần tìm hiểu cú pháp này có thể xem tại XPath Tutorial).

Dữ liệu RSS thường có dạng giống nhau để các trình đọc Feed có thể phân tích được. Trong ví dụ , sẽ lấy dữ liệu từ: http://sports.yahoo.com/top/rss.xml:

<rss version="2.0" xmlns:media="http://search.yahoo.com/mrss/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:atom="http://www.w3.org/2005/Atom">
  <channel>
    <ttl>10</ttl>
    <copyright>Copyright (c) 2012 Yahoo!, Inc. All rights reserved.</copyright>
    <language>en-us</language>
    <title>Yahoo! Sports - Top News</title>
    <link>http://sports.yahoo.com</link>
    <description>Latest news and information from the world of sports.</description>
    <item>
      <media:content url="http://l.yimg.com/iu/api/res/1.2/ZO3GplbTYqLqZRtE2azdKQ--/YXBwaWQ9eXZpZGVvO2ZpPWZpbGw7aD0xMzA7cT04MDt3PTEzMA--/http://media.zenfs.com/en_us/Sports/ap/201209031404506777010-p2.jpeg" height="130" width="130"/>
      <media:credit role="provider">The Associated Press</media:credit>
      <media:text type="html">...</media:text>
      <title>Shields leads Rays past Yankees, tightens AL East (Yahoo! Sports)</title>
      <link>http://us.rd.yahoo.com/sports/rss/top/SIG=12kmstpr5/*http%3A//sports.yahoo.com/news/shields-leads-rays-past-yankees-205933135--mlb.html</link>
      <description>...</description>
      <pubDate>Mon, 03 Sep 2012 15:50:55 PDT</pubDate>
      <category>MLB</category>
      <guid isPermaLink="false">urn:newsml:sports.yahoo,lego:19780928:top,article,f4c647e1-d336-3275-a6f4-2e8d39fe4926-l:1</guid>
    </item>
    <item>
		<!- ... !>
    </item>

    <!- Other item elements !>

    <lastBuildDate>Mon, 03 Sep 2012 21:51:46 PDT</lastBuildDate>
  </channel>
</rss>

Mỗi thẻ item trong dữ liệu XML sẽ tương ứng với một dòng dữ liệu của bảng articles_rss. Bảng này tôi sẽ tạo một unique index là cột link để đảm bảo rằng không có các dòng RSS chỉ đến cùng một link được thêm vào.

CREATE TABLE `articles_rss` (
  `articleid` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(100) NOT NULL,
  `link` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `image` VARCHAR(255) DEFAULT NULL,
  `publisheddate` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`articleid`),
  UNIQUE KEY `link` (`link`)
) ENGINE=MYISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8

Cấu trúc trực quan hơn:

+---------------+--------------+------+-----+---------------------+----------------+
| Field         | Type         | Null | Key | Default             | Extra          |
+---------------+--------------+------+-----+---------------------+----------------+
| articleid     | int(11)      | NO   | PRI | NULL                | auto_increment |
| title         | varchar(100) | NO   |     | NULL                |                |
| link          | varchar(255) | NO   | UNI | NULL                |                |
| description   | text         | YES  |     | NULL                |                |
| image         | varchar(255) | YES  |     | NULL                |                |
| publisheddate | datetime     | NO   |     | 0000-00-00 00:00:00 |                |
+---------------+--------------+------+-----+---------------------+--------------

Tiếp đến tạo một stored procedure để import RSS vào bảng. Dữ liệu truyền vào là nội dung XML mà bạn đã thấy ở trên.
Sử dụng hàm ExtractValue(), tôi sẽ duyệt từng mục phần tử item dựa vào biến đếm i (được sử dụng trong cú pháp XPath là $i). MySQL cung cấp hai hàm sau cho phép bạn sử dụng cú pháp XPath để thao tác với dữ liệu XML:

Đối với trường hợp trùng dữ liệu (dựa vào unique index ‘link’), câu lệnh insert sẽ bỏ qua nhờ từ khóa IGNORE.

DELIMITER $$

DROP PROCEDURE IF EXISTS `articles_rss_import`$$

CREATE PROCEDURE `articles_rss_import`(
	IN xml TEXT
	)
BEGIN
	DECLARE i INT DEFAULT 1;

	SET @dateformat = "%a, %d %M %Y %H:%i:%s PDP";
	SET @timezone = -7;
	# the number of records will be inserted.
	SET @total = (SELECT ExtractValue(xml,"count(//item)"));

	WHILE i <= @total DO
        INSERT IGNORE INTO articles_rss(title, description, link,image, publisheddate) VALUES(
        ExtractValue(xml,"//item[$i]/title"),
        ExtractValue(xml,"//item[$i]/description"),
		ExtractValue(xml,"//item[$i]/link"),
        ExtractValue(xml,"//item[$i]/media:content/@url"),
        COALESCE(DATE_SUB(STR_TO_DATE(ExtractValue(xml,"//item[$i]/pubDate"),@dateformat),INTERVAL @timezone HOUR),CURRENT_TIMESTAMP)
        );

        SET i = i + 1;
    END WHILE;
   SELECT @total;
END$$

DELIMITER ;