Wednesday, August 19, 2009

Creating XML Sitemap from MS SQL

As anyone that has tried to create a sitemap for a decent sized site knows, no tool you can pull off the shelf really works. When you are dealing with hundreds of thousands of URLs on your site, tools that follow your site and try and create an XML sitemap just don’t work very well.
If you want to provide a sitemap to the search engines that have just the URL’s you want and none of the junk or various permutations of a URL a BOT is bound to find, you have just one choice and that is to programmatically develop a file with the output you desire.
I could find no utility on the market that would allow you to feed it a T-SQL query and have it output an XML sitemap. Maybe some day I’ll write it. For now I can accomplish my needs with the stored procedure below.
So what we have here is a stored procedure that does the following:
  • Creates seven sitemaps based on seven different queries. Each sitemap has a specific type of page we want the search engines to follow.
  • Outputs the sitemap to an XML file encoded in UTF-16 (only format MS SQL supports unfortunately).
  • Converts the files from UTF-16 to UTF-8
  • Finally, deletes and names the files
I use the various sitemaps here each for a different purpose. I have one for each domain I manage of the following: product URL’s, category URL’s, and category URL’s restricted to brands.
I used the SQL XML RAW capability to output my query in an XML format. This works REALLY well.
The most time consuming part was trying to cheat my way into getting a UTF-8 formatted file since MS SQL just can’t do anything but UTF-16. There are easier ways to do this in Linux, in Windows I had to find a tool I could use from a command line. I ended up finding a great tool for converting various types of documents from one encoding to another. It’s called Unifier by Melody-Soft.
At this point all that was left was renaming the files to be in a standard format. I do this with xp_cmdshell as you can see in my SP below.
   1: USE [CART]
   2: GO
   3: /****** Object:  StoredProcedure [dbo].[Sp_CreateSitemaps2]    Script Date: 08/19/2009 12:16:34 ******/
   4: SET ANSI_NULLS ON
   5: GO
   6: SET QUOTED_IDENTIFIER ON
   7: GO
   8: -- =============================================
   9: -- Author:        Sean McWherter
  10: -- Create date: 8-10-09
  11: -- Description:    Generates all sitemaps
  12: -- =============================================
  13: ALTER PROCEDURE [dbo].[Sp_CreateSitemaps] 
  14: AS
  15: BEGIN
  16:     -- SET NOCOUNT ON added to prevent extra result sets from
  17:     -- interfering with SELECT statements.
  18:     SET NOCOUNT ON;
  19: --PS PRODUCTS
  20: DECLARE @x_psproduct xml
  21: DECLARE @strXML_psproduct varchar(MAX)
  22: SET @x_psproduct=(
  23:  
  24: select  --top 200
  25: 'http://www.petstore.com/' + replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(case when WebParent is not null then ParentDesc else description end, ' - ', '_'),'-','_') + 
  26: '_' + replace(LongName, '-', '_') +
  27: '-' + replace(ManufacturerName, '-', '_') +
  28: '-' + idProduct +
  29: '-' + a.GPCat4,
  30: '®',''),'.',''),'/','_'),',',''),'& ',''),' ','_'),'&','_'),'™',''),':',''),'>',''),'<',''),'%','_') +
  31: '-vi.html' as loc,
  32: 'daily' as changefreq
  33: --, idproduct, parentdesc, longname, manufacturername
  34: from MDCART_products a inner join MDCART_CategoriesGP b on a.GpCat4 = b.GPCat1 inner join MDCART_Manufacturers c on a.ManufacturerCode = c.ManufacturerCode
  35: where ((a.idProduct = WebParent and ParentDesc is not null)  or WebParent is null) and LongName is not null and description is not null and ManufacturerName is not null and a.GpCat4 is not null and Visable = 1
  36: and LEFT(gpcat4,2) in (select distinct LEFT(gpcat4,2) cats  from MDCART_products where active <> 0 and gpcat4 is not null)
  37: order by a.idProduct desc
  38: for xml raw('url'), ELEMENTS)
  39:  
  40: SET @strXML_psproduct= '<?xml version="1.0" encoding="UTF-8"?>
  41: <urlset      xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"      xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9            http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">'
  42: + CONVERT(varchar(MAX),@x_psproduct) + '</urlset>'
  43:  
  44: --PS CATEGORY FEED
  45: DECLARE @x_pscategory xml
  46: DECLARE @strXML_pscategory varchar(MAX)
  47: SET @x_pscategory=(
  48:  
  49: select  --top 250 
  50: 'http://www.petstore.com/' + replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(LongName, ' - ', '_'),'-','_') + 
  51: '-' + GPCat1,
  52: '®',''),'.',''),'/','_'),',',''),'& ',''),' ','_'),'&','_'),'™',''),':',''),'>',''),'<',''),'%','_') +
  53: '-ct.html' as loc,
  54: 'hourly' as changefreq
  55: from  MDCART_CategoriesGP 
  56: where  Visable = 1 and LongName is not null and GPCat1 is not null
  57: and LEFT(GPCat1,2) in (select distinct LEFT(gpcat4,2) cats  from MDCART_products where active <> 0 and gpcat4 is not null)
  58: order by gpcat1 
  59: for xml raw('url'), ELEMENTS)
  60:  
  61: SET @strXML_pscategory= '<?xml version="1.0" encoding="UTF-8"?>
  62: <urlset      xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"      xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9            http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">'
  63: + CONVERT(varchar(MAX),@x_pscategory) + '</urlset>'
  64:  
  65: --PS CATEGORY W/ BRAND FEED
  66: DECLARE @x_pscategory_brand xml
  67: DECLARE @strXML_pscategory_brand varchar(MAX)
  68: SET @x_pscategory_brand=(
  69:  
  70: select  --top 250 
  71: 'http://www.petstore.com/' + replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(ManufacturerName, ' - ', '_'),'-','_') + 
  72: '-' + LongName +
  73: '-' + ManufacturerCode +
  74: '-' + GPCat1,
  75: '®',''),'.',''),'/','_'),',',''),'& ',''),' ','_'),'&','_'),'™',''),':',''),'>',''),'<',''),'%','_') +
  76: '-ct.html' as loc,
  77: 'hourly' as changefreq
  78: from ( SELECT DISTINCT b.ManufacturerName, c.LongName, b.ManufacturerCode, c.GPCat1
  79:         FROM  MDCART_products AS a INNER JOIN MDCART_Manufacturers AS b ON a.ManufacturerCode = b.ManufacturerCode INNER JOIN 
  80:                 MDCART_CategoriesGP AS c ON a.GpCat4 = c.GPCat1 
  81:         where ManufacturerName is not null and LongName is not null and description is not null and b.ManufacturerCode is not null and c.GPCat1 is not null and Visable =1 
  82:         and LEFT(c.GPCat1,2) in (select distinct LEFT(gpcat4,2) cats  from MDCART_products where active <> 0 and gpcat4 is not null)) as a1
  83: order by ManufacturerName, LongName 
  84: for xml raw('url'), ELEMENTS)
  85:  
  86: SET @strXML_pscategory_brand= '<?xml version="1.0" encoding="UTF-8"?>
  87: <urlset      xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"      xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9            http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">'
  88: + CONVERT(varchar(MAX),@x_pscategory_brand) + '</urlset>'
  89:  
  90: --MD PRODUCTS
  91: DECLARE @x_mdproduct xml
  92: DECLARE @strXML_mdproduct varchar(MAX)
  93: SET @x_mdproduct=(
  94:  
  95: select  --top 200
  96: 'http://www.marinedepot.com/' + replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(case when WebParent is not null then ParentDesc else description end, ' - ', '_'),'-','_') + 
  97: '_' + replace(LongName, '-', '_') +
  98: '-' + replace(ManufacturerName, '-', '_') +
  99: '-' + idProduct +
 100: '-' + a.GPCat1,
 101: '®',''),'.',''),'/','_'),',',''),'& ',''),' ','_'),'&','_'),'™',''),':',''),'>',''),'<',''),'%','_') +
 102: '-vi.html' as loc,
 103: 'daily' as changefreq
 104: --, idproduct, parentdesc, longname, manufacturername
 105: from MDCART_products a inner join MDCART_CategoriesGP b on a.GPCat1 = b.GPCat1 inner join MDCART_Manufacturers c on a.ManufacturerCode = c.ManufacturerCode
 106: where ((a.idProduct = WebParent and ParentDesc is not null)  or WebParent is null) and LongName is not null and description is not null and ManufacturerName is not null and a.GPCat1 is not null and Visable = 1
 107: and LEFT(a.GPCat1,2) in (select distinct LEFT(gpcat1,2) cats  from MDCART_products where active = -1 and gpcat1 is not null)
 108: order by a.idProduct desc
 109: for xml raw('url'), ELEMENTS)
 110:  
 111: SET @strXML_mdproduct= '<?xml version="1.0" encoding="UTF-8"?>
 112: <urlset      xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"      xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9            http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">'
 113: + CONVERT(varchar(MAX),@x_mdproduct) + '</urlset>'
 114:  
 115: --MD CATEGORY FEED
 116: DECLARE @x_mdcategory xml
 117: DECLARE @strXML_mdcategory varchar(MAX)
 118: SET @x_mdcategory=(
 119:  
 120: select  --top 250 
 121: 'http://www.marinedepot.com/' + replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(LongName, ' - ', '_'),'-','_') + 
 122: '-' + GPCat1,
 123: '®',''),'.',''),'/','_'),',',''),'& ',''),' ','_'),'&','_'),'™',''),'>',''),'<',''),'%','_') +
 124: '-ct.html' as loc,
 125: 'hourly' as changefreq
 126: from  MDCART_CategoriesGP 
 127: where  Visable = 1 and LongName is not null  and GPCat1 is not null
 128: and LEFT(GPCat1,2) in (select distinct LEFT(gpcat1,2) cats  from MDCART_products where active = -1 and gpcat1 is not null)
 129: order by gpcat1 
 130: for xml raw('url'), ELEMENTS)
 131:  
 132: SET @strXML_mdcategory= '<?xml version="1.0" encoding="UTF-8"?>
 133: <urlset      xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"      xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9            http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">'
 134: + CONVERT(varchar(MAX),@x_mdcategory) + '</urlset>'
 135:  
 136: --MD CATEGORY W/ BRAND FEED
 137: DECLARE @x_mdcategory_brand xml
 138: DECLARE @strXML_mdcategory_brand varchar(MAX)
 139: SET @x_mdcategory_brand=(
 140:  
 141: select  --top 250 
 142: 'http://www.marinedepot.com/' + replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(ManufacturerName, ' - ', '_'),'-','_') + 
 143: '-' + LongName +
 144: '-' + ManufacturerCode +
 145: '-' + GPCat1,
 146: '®',''),'.',''),'/','_'),',',''),'& ',''),' ','_'),'&','_'),'™',''),':',''),'>',''),'<',''),'%','_') +
 147: '-ct.html' as loc,
 148: 'hourly' as changefreq
 149: from ( SELECT DISTINCT b.ManufacturerName, c.LongName, b.ManufacturerCode, c.GPCat1
 150:         FROM  MDCART_products AS a INNER JOIN MDCART_Manufacturers AS b ON a.ManufacturerCode = b.ManufacturerCode INNER JOIN 
 151:                 MDCART_CategoriesGP AS c ON a.gpcat1 = c.GPCat1 
 152:         where ManufacturerName is not null and LongName is not null and description is not null and b.ManufacturerCode is not null and c.GPCat1 is not null and Visable =1 
 153:         and LEFT(c.GPCat1,2) in (select distinct LEFT(gpcat1,2) cats  from MDCART_products where active = -1 and gpcat1 is not null)) as a1
 154: order by ManufacturerName, LongName 
 155: for xml raw('url'), ELEMENTS)
 156:  
 157: SET @strXML_mdcategory_brand= '<?xml version="1.0" encoding="UTF-8"?>
 158: <urlset      xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"      xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9            http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">'
 159: + CONVERT(varchar(MAX),@x_mdcategory_brand) + '</urlset>'
 160:  
 161: --MD AQUARIUM PAGE FEED
 162: DECLARE @x_mdaquarium xml
 163: DECLARE @strXML_mdaquarium varchar(MAX)
 164: SET @x_mdaquarium=(
 165:  
 166: select  --top 250 
 167: 'http://www.marinedepot.com/' + replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(idPage, ' - ', '_'),'-','_'),
 168: '®',''),'.',''),'/','_'),',',''),'& ',''),' ','_'),'&','_'),'™',''),':',''),'>',''),'<',''),'%','_') +
 169: '-ap.html' as loc,
 170: 'daily' as changefreq
 171: from MDCART_Aquarium_Page
 172: order by idPage
 173: for xml raw('url'), ELEMENTS)
 174:  
 175: SET @strXML_mdaquarium= '<?xml version="1.0" encoding="UTF-8"?>
 176: <urlset      xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"      xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9            http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">'
 177: + CONVERT(varchar(MAX),@x_mdaquarium) + '</urlset>'
 178: ------- OUTPUT
 179: exec xp_cmdshell 'del \\servername\sitemappath\SiteMaps\*.xml', no_output
 180: exec spWriteStringToFile @strXML_psproduct, '\\sangbe\WWW_Live\petstore.com\IMD\SiteMaps', 'utf16-psproduct.xml'
 181: exec spWriteStringToFile @strXML_pscategory, '\\sangbe\WWW_Live\petstore.com\IMD\SiteMaps', 'utf16-pscategory.xml'
 182: exec spWriteStringToFile @strXML_pscategory_brand, '\\sangbe\WWW_Live\petstore.com\IMD\SiteMaps', 'utf16-pscategory_brand.xml'
 183: exec spWriteStringToFile @strXML_mdproduct, '\\sangbe\WWW_Live\petstore.com\IMD\SiteMaps', 'utf16-mdproduct.xml'
 184: exec spWriteStringToFile @strXML_mdcategory, '\\sangbe\WWW_Live\petstore.com\IMD\SiteMaps', 'utf16-mdcategory.xml'
 185: exec spWriteStringToFile @strXML_mdcategory_brand, '\\sangbe\WWW_Live\petstore.com\IMD\SiteMaps', 'utf16-mdcategory_brand.xml'
 186: exec spWriteStringToFile @strXML_mdaquarium, '\\sangbe\WWW_Live\petstore.com\IMD\SiteMaps', 'utf16-mdaquarium.xml'
 187: exec xp_cmdshell 'C:\Unifier\xunifier.exe /c "\\servername\sitemappath\SiteMaps\config1.ucp" /l "\\servername\sitemappath\SiteMaps\sitemapconversion.log" /y "\\servername\sitemappath\SiteMaps\utf16*.xml"', no_output
 188: exec xp_cmdshell 'rename \\servername\sitemappath\SiteMaps\output_utf16-psproduct.xml psproduct.xml', no_output
 189: exec xp_cmdshell 'rename \\servername\sitemappath\SiteMaps\output_utf16-pscategory.xml pscategory.xml', no_output
 190: exec xp_cmdshell 'rename \\servername\sitemappath\SiteMaps\output_utf16-pscategory_brand.xml pscategory_brand.xml', no_output
 191: exec xp_cmdshell 'rename \\servername\sitemappath\SiteMaps\output_utf16-mdproduct.xml mdproduct.xml', no_output
 192: exec xp_cmdshell 'rename \\servername\sitemappath\SiteMaps\output_utf16-mdcategory.xml mdcategory.xml', no_output
 193: exec xp_cmdshell 'rename \\servername\sitemappath\SiteMaps\output_utf16-mdcategory_brand.xml mdcategory_brand.xml', no_output
 194: exec xp_cmdshell 'rename \\servername\sitemappath\SiteMaps\output_utf16-mdaquarium.xml mdaquarium.xml', no_output
 195: exec xp_cmdshell 'del \\servername\sitemappath\SiteMaps\utf16*.xml', no_output
 196: END
Finally this last snippet shows the syntax I used for pinging the biggest search engines to let them know I’ve updated my sitemap. I have a SQL job that runs nightly. It first executes this SP, then pings each search engine. Below is a sample for Google. Just create a step in your job formatted like you see below to run this javascript.
   1: var WebAddress = "http://www.google.com/webmasters/tools/ping?sitemap=http://www.marinedepot.com/sitemapfolder/mdaquarium.xml";
   2: function main()
   3: {
   4:     var xmlHttpRequest = CreateObject("Microsoft.XMLHTTP");   
   5:     xmlHttpRequest.open("GET", WebAddress, false);
   6: }
   7:  
   8: var WebAddress2 = "http://www.google.com/webmasters/tools/ping?sitemap=http://www.marinedepot.com/sitemapfolder/mdaquarium.xml";
   9: function main2()
  10: {
  11:     var xmlHttpRequest2 = CreateObject("Microsoft.XMLHTTP");   
  12:     xmlHttpRequest2.open("GET", WebAddress2, false);
  13: }
  14:  
  15: var WebAddress3 = "http://www.google.com/webmasters/tools/ping?sitemap=http://www.marinedepot.com/sitemapfolder/mdcategory_brand.xml";
  16: function main3()
  17: {
  18:     var xmlHttpRequest3 = CreateObject("Microsoft.XMLHTTP");   
  19:     xmlHttpRequest3.open("GET", WebAddress3, false);
  20: }
  21:  
  22: var WebAddress4 = "http://www.google.com/webmasters/tools/ping?sitemap=http://www.marinedepot.com/sitemapfolder/mdproduct.xml";
  23: function main4()
  24: {
  25:     var xmlHttpRequest4 = CreateObject("Microsoft.XMLHTTP");   
  26:     xmlHttpRequest4.open("GET", WebAddress4, false);
  27: }
  28:  
  29: var WebAddress5 = "http://www.google.com/webmasters/tools/ping?sitemap=http://www.petstore.com/sitemapfolder/pscategory.xml";
  30: function main5()
  31: {
  32:     var xmlHttpRequest5 = CreateObject("Microsoft.XMLHTTP");   
  33:     xmlHttpRequest5.open("GET", WebAddress5, false);
  34: }
  35:  
  36: var WebAddress6 = "http://www.google.com/webmasters/tools/ping?sitemap=http://www.petstore.com/sitemapfolder/pscategory_brand.xml";
  37: function main6()
  38: {
  39:     var xmlHttpRequest6 = CreateObject("Microsoft.XMLHTTP");   
  40:     xmlHttpRequest6.open("GET", WebAddress6, false);
  41: }
  42:  
  43: var WebAddress7 = "http://www.google.com/webmasters/tools/ping?sitemap=http://www.petstore.com/sitemapfolder/psproduct.xml";
  44: function main7()
  45: {
  46:     var xmlHttpRequest7 = CreateObject("Microsoft.XMLHTTP");   
  47:     xmlHttpRequest7.open("GET", WebAddress7, false);
  48: }
image
Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)