Google Product Feed
I spent a fair amount of time crafting a google shopping feed that lists all products/variants/colors with images for each specific product/variant/color combination and grouped the colors together in a single variant per Google's specs (as of 6/30/12). Also includes additional images for each color if exists. I've hacked the inventory table to make this all happen. Using warehouselocation, extendeddata, and vendorfullsku to populate the gtin and to be able to query on each color. I couldn't find a way to make this happen in a query any other way since the colors are kept in a single, comma-delimited field of the variant and also I couldn't find another way to access the product/variant/color-specific images except with the hacked field and manually typing in the color name that ASPDNSF uses based on the pretty color name.
This is highly customized for just my site and only works with a fair amount of manual data entry in the hi-jacked fields, but worked for me since I only have 20 products on my site (www.cm4.com). Hopefully this will help someone as they're trying to accomplish a similar task.
Code:<?xml version="1.0" encoding="utf-8" ?> <!-- ###################################################################################################### --> <!-- Copyright AspDotNetStorefront.com, 1995-2009. All Rights Reserved. --> <!-- http://www.aspdotnetstorefront.com --> <!-- For details on this license please visit the product homepage at the URL above. --> <!-- THE ABOVE NOTICE MUST REMAIN INTACT. --> <!-- ###################################################################################################### --> <package displayname="Google Base RSS 2.0 Feed" version="2.1" debug="false" includeentityhelper="false"> <query name="Products" rowElementName="Product"> <sql> <![CDATA[ select p.productid, pv.variantid, p.sku, i.vendorfullsku as id, p.name + ' (' + i.vendorid + ')' as title, isnull(p.FroogleDescription, '') as description, p.extensiondata as google_product_category, p.extensiondata as product_type, i.warehouselocation as imagecolor, i.extensiondata as gtin, pv.name as item_group_id, i.vendorid as color, p.misctext as material, convert(VARCHAR(10), pv.weight) + 'lb' as shipping_weight, pv.price, p.sename, p.SEKeywords, m.Name as brand from dbo.product p with (nolock) join dbo.productvariant pv with (nolock) on p.productid = pv.productid join (Select ProductID, MIN(CategoryID) as CategoryID from productcategory group by ProductID) pc on p.ProductID = pc.ProductID join category c with (nolock) on pc.CategoryID = c.CategoryID left join ProductManufacturer pm with (nolock) on p.ProductID = pm.ProductID left join Manufacturer m with (nolock) on pm.ManufacturerID = m.ManufacturerID join Inventory i with (nolock) on pv.variantid = i.variantid where p.IsSystem=0 and p.deleted = 0 and p.published = 1 and p.ExcludeFromPriceFeeds = 0 and c.ParentCategoryID = 0 and i.vendorfullsku <> '' order by p.productid ]]> </sql> </query> <PackageTransform> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" xmlns:g="http://base.google.com/ns/1.0" exclude-result-prefixes="aspdnsf"> <xsl:output method="xml" omit-xml-declaration="no" encoding="utf-8" indent="no" /> <xsl:param name="EntityName"> <xsl:value-of select="/root/QueryString/entity" /> </xsl:param> <xsl:param name="ShowInGrid">true</xsl:param> <xsl:param name="GridCols">4</xsl:param> <xsl:param name="UseDescr" select="aspdnsf:AppConfig('Froogle_UseDescriptionsIfFroogleDescEmpty')" /> <xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" /> <xsl:param name="WebConfigLocaleSetting" select="/root/Runtime/WebConfigLocaleSetting" /> <xsl:template match="/"> <rss version="2.0"> <channel> <title> <xsl:value-of select="aspdnsf:AppConfig('StoreName')" /> </title> <link> <xsl:value-of select="concat('http',substring-after(/root/System/StoreUrl,'https'))" /> </link> <description> <xsl:value-of select="aspdnsf:AppConfig('RSSChannelDescr')" /> </description> <language> <xsl:value-of select="$WebConfigLocaleSetting" /> </language> <pubDate> <xsl:value-of select="/root/System/CurrentDateTime" /> </pubDate> <lastBuildDate> <xsl:value-of select="/root/System/CurrentDateTime" /> </lastBuildDate> <xsl:apply-templates select="/root/Products/Product" /> </channel> </rss> </xsl:template> <xsl:template name="AdditionalImages"> <xsl:param name="imageNum" select="1"></xsl:param> <xsl:param name="productid" select="aspdnsf:GetMLValue(productid)"></xsl:param> <xsl:param name="imagecolor" select="aspdnsf:GetMLValue(imagecolor)"></xsl:param> <xsl:param name="image"><xsl:text>../../shop/images/Product/large/</xsl:text><xsl:value-of select="$productid" /><xsl:text>_</xsl:text><xsl:value-of select="$imageNum" /><xsl:text>_</xsl:text><xsl:value-of select="$imagecolor" /><xsl:text>.jpg</xsl:text></xsl:param> <xsl:param name="imageURL"><xsl:value-of select="concat('http',substring-after(/root/System/StoreUrl,'https'))" /><xsl:text>images/Product/large/</xsl:text><xsl:value-of select="$productid" /><xsl:text>_</xsl:text><xsl:value-of select="$imageNum" /><xsl:text>_</xsl:text><xsl:value-of select="$imagecolor" /><xsl:text>.jpg</xsl:text></xsl:param> <xsl:if test="$imageNum < 11"> <xsl:if test="aspdnsf:FileExists($image)"> <g:additional_image_link> <xsl:value-of select="$imageURL" /> </g:additional_image_link> </xsl:if> <xsl:call-template name="AdditionalImages"> <xsl:with-param name="imageNum" select="$imageNum + 1"/> <xsl:with-param name="productid" select="$productid"/> <xsl:with-param name="imagecolor" select="$imagecolor"/> </xsl:call-template> </xsl:if> </xsl:template> <xsl:template match="Product"> <xsl:param name="productid" select="aspdnsf:GetMLValue(productid)"></xsl:param> <xsl:param name="VariantID" select="aspdnsf:GetMLValue(variantid)"></xsl:param> <xsl:param name="SKU" select="aspdnsf:GetMLValue(SKU)"></xsl:param> <xsl:param name="id" select="aspdnsf:GetMLValue(id)"></xsl:param> <xsl:param name="title" select="aspdnsf:GetMLValue(title)"></xsl:param> <xsl:param name="description" select="aspdnsf:GetMLValue(description)"></xsl:param> <xsl:param name="google_product_category" select="aspdnsf:GetMLValue(google_product_category)"></xsl:param> <xsl:param name="product_type" select="aspdnsf:GetMLValue(product_type)"></xsl:param> <xsl:param name="imagecolor" select="aspdnsf:GetMLValue(imagecolor)"></xsl:param> <xsl:param name="gtin" select="aspdnsf:GetMLValue(gtin)"></xsl:param> <xsl:param name="item_group_id" select="aspdnsf:GetMLValue(item_group_id)"></xsl:param> <xsl:param name="color" select="aspdnsf:GetMLValue(color)"></xsl:param> <xsl:param name="material" select="aspdnsf:GetMLValue(material)"></xsl:param> <xsl:param name="shipping_weight" select="aspdnsf:GetMLValue(shipping_weight)"></xsl:param> <xsl:param name="price" select="aspdnsf:GetMLValue(price)"></xsl:param> <xsl:param name="sename" select="aspdnsf:GetMLValue(sename)"></xsl:param> <xsl:param name="Keywords" select="aspdnsf:GetMLValue(SEKeywords)"></xsl:param> <xsl:param name="brand" select="aspdnsf:GetMLValue(brand)"></xsl:param> <item> <g:id> <xsl:value-of select="$id" /> </g:id> <g:title> <xsl:value-of select="$title" /> </g:title> <g:description> <xsl:value-of select="$description" /> </g:description> <g:google_product_category> <xsl:value-of select="$google_product_category" /> </g:google_product_category> <g:product_type> <xsl:value-of select="$product_type" /> </g:product_type> <link> <xsl:value-of select="concat('http',substring-after(/root/System/StoreUrl,'https'))" /> <xsl:value-of select="aspdnsf:ProductLink(productid, sename, 0, '')" /> </link> <g:image_link> <xsl:value-of select="concat('http',substring-after(/root/System/StoreUrl,'https'))" /><xsl:text>images/Product/large/</xsl:text><xsl:value-of select="$productid" /><xsl:text>_1_</xsl:text><xsl:value-of select="$imagecolor" /><xsl:text>.jpg</xsl:text> </g:image_link> <xsl:call-template name="AdditionalImages"> <xsl:with-param name="imageNum" select="2"/> <xsl:with-param name="productid" select="$productid"/> <xsl:with-param name="imagecolor" select="$imagecolor"/> </xsl:call-template> <g:condition>New</g:condition> <g:availability>in stock</g:availability> <g:price> <xsl:value-of select="format-number((price), '###0.00')" /> </g:price> <g:brand> <xsl:value-of select="$brand" /> </g:brand> <g:gtin> <xsl:value-of select="$gtin" /> </g:gtin> <g:mpn> <xsl:value-of select="$id" /> </g:mpn> <g:item_group_id> <xsl:value-of select="$item_group_id" /> </g:item_group_id> <g:color> <xsl:value-of select="$color" /> </g:color> <g:material> <xsl:value-of select="$material" /> </g:material> </item> </xsl:template> </xsl:stylesheet> </PackageTransform> </package>
Originally Posted by maltschul
Copyright © 2012, AspDotNetStorefront. All rights reserved.