How to import a Google Sitemap into a MySQL table

May 30, 2018

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

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.

    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>'