[IMPORT XML] | How to import sitemap into Excel or Google sheets

Importing sitemap into your google sheets can save a lot of time in terms of copy pasting and processing. Fortunately, in excel you can import your sitemap automatically without any hassles.

Steps to import sitemap into sheets-

1. Create a new google sheet.
2. Enter the sitemap URL. You can find your sitemap at yourwebsite/sitemap.xml
3.  Copy one of the pages, or post sitemap URLs into the excel sheet. For example, https://www.aozata.com/post-sitemap.xml and paste it into a cell.
4. Paste the following formula into another cell. (this will work if you are using the yoast sitemap).

   =IMPORTXML(C4,"//*[local-name() ='url']/*[local-name() ='loc']")

5. If your sitemap, URL is in C4, then the above formula will get the sitemap URLs in C5 and the below columns.
6. Here is a link to the SITEMAP IMPORT XML feature.