Excel to XML Sitemap Tutorial

The following is a simple way to output Excel data to a static XML text file.

This is not the best solution for video XML sitemaps. Ideally, a video sitemap would be dynamically created by the website’s CMS, or via a plugin, or other automated means. However, this tutorial provides a quick and dirty way to fulfill requests for static XML sitemaps for anything from testing to production. I would not use this to manage a large inventory of URLs, like a content archive, eCommerce catalog, or anything with frequently expanding content.

This tutorial is helpful for making quick XML sitemap files from data that has been structured in Excel.

search engines aware of a reasonable number of new pages, and can also act as a manual inventory of content assets that you intend to be indexed externally.

This process describes making a video XML sitemap file. Google recommends using video markup for JSON-LD. Because of Google’s recommendation, I feel like we won’t get a ton of these requests in the future. The difference between the following video XML tutorial and other types of sitemaps (universal, news, images, etc) will be the <urlset> value and the specific information that is required for each sitemap type.

Gather Video Data into a Spreadsheet

The first step in building a static video sitemap is to gather all of the data related to the videos. This is typically a manual process. The required data fields for video sitemaps follow:

• Title
• Description
• Embed page URL
• Thumbnail URL
• Raw video file or video player URL

Write an XML Template File

In a text editor, create an XML file template with two entries. This XML file will provide the structure for the video sitemap, and so it is important that it is written in the proper syntax and must not contain any errors. A guideline for how video XML sitemap tags should be formatted is available here.

<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"        xmlns:video="http://www.google.com/schemas/sitemap-video/1.1">    <url>        <loc></loc>        <video:video>            <video:thumbnail_loc></video:thumbnail_loc>            <video:title></video:title>            <video:description></video:description>            <video:player_loc></video:player_loc>        </video:video>    </url>    <url>        <loc></loc>        <video:video>            <video:thumbnail_loc></video:thumbnail_loc>            <video:title></video:title>            <video:description></video:description>            <video:player_loc></video:player_loc>        </video:video>    </url> </urlset>

Save this file as video-sitemap-template.xml.

Import the XML Template Into Excel Developer Tools

Enable Excel Developer Tools

Developer tools are not available in Excel by default. To add a Developer tab to Excel follow these steps:

1. Right click on the Excel toolbar menu and select Customize the Ribbon…
   
   
2. Check the Developer box in the following menu and click OK
   
   

Import the XML Template

In the Developer tab…

1. Click Source
2. Click XML Maps… in the XML Source window that appears on the right
3. Click Add… in the following XML Maps window
   
4. Navigate to the XML template file and click Open
5. Click OK n the following dialog box
   
6. Click OK in the XML Maps window
   

Associate XML Tag Fields with the Corresponding Column

At this point, you will see your XML template tags in the XML Source window on the right. Click and drag each tag to the top row of the corresponding column in your spreadsheet. When you do this, each column will be formatted as a table.

Export the XML Sitemap

1. Click Export in the Excel Developer Tools menu
2. Save the file as video-sitemap_{client-name}_ and click Export