How to import a Google Sitemap into a MySQL table

May 30th, 2018

Warning: This post is 6 years old. Some of this information may be out of date.

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