Sitemap of pages generated via custom table data

Romina Herrera asked on September 17, 2019 15:17

I've had great success customizing my sitemap with the Sitemap webpart and specifying my custom page types and the directories, however for a great deal of pages, we generate them using information stored in custom tables, and these cannot be loaded into the XML Sitemap webpart.

I can generate the list of urls I need with a custom query. But how can I have that page trigger the xml response so it's actually read as an xml sitemap and not a content page?

Thank you.

Recent Answers


Peter Mogilnitski answered on September 17, 2019 17:09

Well technically there are 2 ways - one is to clone and modify site map xml web part. Second is to use custom response Web part and macro to get a data from custom table.

You must set up the proper mime response type i.e. application/xml

Here is a macro sample :

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
{% GlobalObjects.CustomTables["customtable.SampleTable"].Items.ApplyTransformation("<url><loc>{#ItemUrl#}</loc> <lastmod>{#ItemLastModified#}</lastmod><changefreq>monthly</changefreq><priority>0.8</priority></url>") #%}
</urlset> 
0 votesVote for this answer Mark as a Correct answer

Romina Herrera answered on September 18, 2019 15:21 (last edited on September 18, 2019 15:25)

Hi Peter, thank you for your response. I tried using your second option, but I ran into some limitations. The url for these pages is created by combining columns in the table for example: /productNumber/productName-category.aspx

With the second approach how can I: 1. convert column data to string? 2. pull data from a column in a secondary table? (category is stored in a separate table) 3. only load table rows that are "active"? (this is controlled by a boolean checkbox)

Given all these requirements, I am now going to try method #1, which is to clone and modify the Sitemap webpart.

0 votesVote for this answer Mark as a Correct answer

Romina Herrera answered on October 2, 2019 21:17

Hi Peter, here I am again, I've mode some progress but I'm still stuck. I've modified the Google XML Webpart, adding controls to select a query, a custom table, and a transformation. Now, the page succesfully loads as an xml page, and it is finding items in the table, but url portion created by information in the columns is empty.

What I get:

<url><loc>https://www.mysite.org/directory/en//--.aspx
      </loc><lastmod/>

The transformation I'm using for my items is:

<url>    
    <loc>https://www.mysite.org/directory/{%CMS.DocumentEngine.DocumentContext.CurrentDocumentCulture.CultureAlias%}/{%Object.NPI%}/{%Object.FirstName%}-{%Object.LastName"%}-{%SPECIALTIESEN%}.aspx
      </loc>
      <lastmod>{% ItemModifiedWhen.Format("yyyy-MM-dd") %}</lastmod> 
</url>

The QUERY on the table is asking for:

SELECT ##TOPN## * FROM (Select  dbo.custom_MCHS_Physicians.NPI, 
                        dbo.custom_MCHS_Physicians.FullName, 
                        dbo.custom_MCHS_Physicians.FirstName, 
                        dbo.custom_MCHS_Physicians.LastName, 
                        dbo.custom_MCHS_Physicians.ActiveFlag,
                        dbo.custom_MCHS_Physicians.PSA

FROM  dbo.custom_MCHS_Physicians INNER JOIN
dbo.custom_MCHS_SpecToPhys ON dbo.custom_MCHS_Physicians.NPI = dbo.custom_MCHS_SpecToPhys.NPI
As T Where ##WHERE##

I'm not very familiar with queries and I'm copying from other components already used in the site, so I'm not certain what else is missing or what I need to correct so that the columns data load in the transformation.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.