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

=IMPORTXML("", "//*[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.

=IMPORTXML("", "//title/text()")

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.

=IMPORTXML("", "//meta[@name='description']/@content")


A single golf clap? Or a long standing ovation?

By clapping more or less, you can signal to us which stories really stand out.