Analyse sitemap.xml file with importXML in Google Spreadsheets
I have to work with sitemap.xml file quite often, but I cannot remember the formula so well. So I note them down here, for my future work and for other people if you find it’s helpful.
This code work with Google Spreadsheets.
Import all URL in sitemap.xml file
This code get all the URL in sitemap file. Please replace the link to your sitemap to https://www.example.com/sitemap.xml
=IMPORTXML("https://www.example.com/sitemap.xml", "//*[local-name() ='url']/*[local-name() ='loc']")
Get the title of page from URL
This code get the title of the page. The title is placed inside <title> tag.
Get the meta description of page from URL
This code get the meta description of page. Meta description is placed inside <meta name=”description” content=”sample content”> tag.
I wanted to get my sitemap data into Google Sheets for a few content audit and statistics dashboards. The IMPORTXML(…benellwood.co.uk