ImportXML Sitemap Error

1
votes

I'm trying to import a sitemap's URLS into a google sheet using:

=IMPORTXML("http://www.websiteurl.com/sitemap.xml","//url/loc")

And constantly getting the error:

"Error: Imported Xml content can not be parsed."

Upon searching and being told to use the old sheets because of a reported bug using ImportXML in the new sheets, and then also trying everything else under the sun, I continue to get this error no matter what.

Any ideas? TIA!

3
xpathgoogle-sheetssitemap
Is http://www.websiteurl.com/sitemap.xml your actual URL? I doubt it. We need to see the actual URL you are importing from. Same goes for the path expression. - Mathias Müller
Well the current sitemap I'm trying to grab is turn-keytechnologies.com/sitemap.xml, but I've tried multiple different sites and paths and nothing seems to work. - Danny Lopez

3 Answers

2
votes

I've had the same problem this morning. It can be resolved using local-name() which does something magical to ignore the namespace.

=IMPORTXML("http://www.turn-keytechnologies.com/sitemap.xml","//*[local-name() ='url']/*[local-name() ='loc']")

Source: http://benellwood.co.uk/2015/06/26/import-a-google-sitemap-xml-file-into-google-sheets/

0
votes

It an be solved by using 

=IMPORTDATA("sitemapurl")

or if you want it to list them for you:

=transpose(split(IMPORTDATA("http://www.turn-keytechnologies.com/sitemap.xml"),">"))
0
votes

Importxml() is notoriously buggy so I created an apps script custom function to solve this. 

sitemap(sitemapUrl, namespace)

The source code is here along with a video for anyone else having issues. https://opensourceseo.org/extract-urls-xml-sitemaps-google-sheets/