How to import a Google Sitemap into a MySQL table

Image for How to import a Google Sitemap into a MySQL table from unsplash.com

By Andrew McCombe
May 30, 2018


Here's a quick snippet showing how to import a Google Sitemap into MySQL. This is useful if you need to do anything with the urls such as scrape the pages or check for 301/302/404 results.

```BASH mkdir sitemaps cd sitemaps wget https://mywebsite.com/sitemap.xml

// the cli param --local-infile is needed, else you will get 
// 'The used command is not allowed with this MySQL version'
mysql -u root -p --local-infile db_name

CREATE TABLE google_sitemap (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `loc` VARCHAR(255) NOT NULL,
    `lastmod` DATE NOT NULL, 
    `changefreq` VARCHAR(16) NULL, 
    `priority` DECIMAL(5,2)
);

LOAD XML LOCAL INFILE 'sitemap.xml' 
INTO TABLE google_sitemap ROWS IDENTIFIED BY '<url>'
```