Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

Learn more about...

AspDotNetStorefront
DotFeed

trying to get product sku in xml package

I'm trying to adapt the old google feed xml package to use with another feed. I need to have the product sku.  It seems like this shouldn't be to hard but t I don't know what it is called in the database. The only other thing i could find is in 'product.VariantsInRightBar.xml.config' which has '<xsl:variable name="fullSku" select="concat($pSKU, SkuSuffix)" />' The SkuSuffix is really what I want I think. The XML package below results in the follwing:

 

<item>
	<title>D'Addario EXL110 Reg Light .010-.046 Strings</title>
	<link>https://www.themusicden.com//p-229202-daddario-exl110-reg-light-010-046-strings.aspx</link>
	<description>D'Addario EXL110 Reg Light .010-.046 Strings</description>
	<g:image_link>http://www.themusicden.com/images/Product/medium/1002-EXL110.gi</g:image_link>
	<g:id></g:id>
	<g:skusuffix></g:skusuffix>
	<g:sku></g:sku>
	
	<g:productid>229202</g:productid>
	<g:fullsku>1002-EXL110</g:fullsku>
	
	<g:brand>D'Addario</g:brand>
	<g:price>5.99</g:price>
	<g:condition>New</g:condition>
	<g:availability>in stock</g:availability>
</item>

 

This  <g:fullsku>1002-EXL110</g:fullsku> ends up being the same for every item in the feed and it isn't the suffix?

 

XML Package:

<?xml version="1.0" encoding="utf-8" ?>
<!-- ###################################################################################################### -->
<!-- Copyright AspDotNetStorefront.com, 1995-2011.  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, 
				p.name, 
				p.description, 
				p.sename, 
				p.ImageFileNameOverride, 
				p.ManufacturerPartNumber, 
				p.SKU,
				isnull(p.FroogleDescription, '') ProductFroogleDescription, 
				isnull(pv.Weight, 0) Weight,
				CONVERT(money, pv.price) AS price,
				CONVERT(money, isnull(pv.MSRP, pv.price) ) AS MSRP,
				pv.inventory,
				CONVERT(money, isnull(pv.saleprice, 0) ) AS saleprice, 
				isnull(pv.FroogleDescription, '') VariantFroogleDescription, 
				isnull(pv.description, '') VariantDescr, 
				m.Name AS ManName,
				p.ExtensionData ProductFeedData,
				pv.ExtensionData VariantFeedData
				from product p with (NOLOCK) 
				LEFT JOIN ProductCategory pc with (NOLOCK) ON p.ProductID = pc.ProductID 
				LEFT 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 
				INNER JOIN ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
				LEFT JOIN (select variantid, sum(quan) inventory from dbo.inventory with (NOLOCK) group by variantid) i on pv.variantid = i.variantid
				LEFT JOIN TaxClass ON p.TaxClassID = TaxClass.TaxClassID
				where 
				p.Published = 1
				and p.IsSystem=0 
				and p.Deleted = 0 
				and p.ExcludeFromPriceFeeds = 0
				and p.ShowBuyButton = 1
				and p.RequiresRegistration = 0                                        
				and pv.isdefault = 1
				and pv.published = 1
				and pv.deleted=0
				and pv.inventory >= 1
            ]]>
      </sql>
      <queryparam paramname="@HideProductsWithLessThanThisInventoryLevel" paramtype="appconfig" requestparamname="HideProductsWithLessThanThisInventoryLevel" sqlDataType="int" defvalue="0"  validationpattern="" />
   </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="/root/System/StoreUrl" />
                  </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 match="Product">
            <xsl:param name="ProductID" select="aspdnsf:GetMLValue(productid)"></xsl:param>
            <xsl:param name="Brand" select="aspdnsf:GetMLValue(ManName)"></xsl:param>
            <xsl:param name="SkuSuffix" select="aspdnsf:GetMLValue(skusuffix)"></xsl:param>
            <xsl:param name="Sku" select="aspdnsf:GetMLValue(Sku)"></xsl:param>
            <xsl:param name="pSKU" select="/root/Products/Product/SKU"></xsl:param>
            <!--<xsl:param name="mpn" select="concat(aspdnsf:GetMLValue(ManufacturerPartNumber), aspdnsf:GetMLValue(VariantManufacturerPartNumber))"></xsl:param>
               <xsl:param name="MPN" select="aspdnsf:GetMLValue(ManufacturerPartNumber)"></xsl:param>
               <xsl:param name="VarMPN" select="aspdnsf:GetMLValue(VariantManufacturerPartNumber)"></xsl:param>
               <xsl:param name="FinalMPN" select="concat(MPN,VarMPN)"></xsl:param> -->
            <xsl:param name="ProductName" select="aspdnsf:GetMLValue(name)"></xsl:param>
            <xsl:param name="ProductVariantName" select="aspdnsf:GetMLValue(VariantName)"></xsl:param>
            <xsl:param name="ProductDescr" select="aspdnsf:GetMLValue(description)"></xsl:param>
            <xsl:param name="VariantDescr" select="aspdnsf:GetMLValue(VariantDescr)"></xsl:param>
            <xsl:param name="ProductFroogleDescription" select="aspdnsf:GetMLValue(ProductFroogleDescription)"></xsl:param>
            <xsl:param name="VariantFroogleDescription" select="aspdnsf:GetMLValue(VariantFroogleDescription)"></xsl:param>
            <xsl:param name="ItemImage" select="aspdnsf:ProductImageUrl(productid, ImageFileNameOverride, SKU, 'medium', 1)"></xsl:param>
            <xsl:param name="ManufacturerPartNumber" select="aspdnsf:GetMLValue(ManufacturerPartNumber)"></xsl:param>
            <xsl:param name="fullSku" select="concat($pSKU, SkuSuffix)"></xsl:param>
            <!--<xsl:variable name="fullSku" select="concat($pSKU, SkuSuffix)" />-->
            <!--<xsl:variable name="fullSku" select="concat($pSKU, SkuSuffix)" />-->
            <item>
               <title>
                  <xsl:choose>
                     <xsl:when test="$ProductName!=''">
                        <xsl:value-of select="$ProductName" />
                     </xsl:when>
                     <xsl:otherwise>
                        <xsl:value-of select="$ProductVariantName" />
                     </xsl:otherwise>
                  </xsl:choose>
               </title>
               <link>
               <xsl:value-of select="/root/System/StoreUrl" />
               <xsl:value-of select="aspdnsf:ProductLink(productid, sename, 0, '')" />
               </link>
               <description>
                  <xsl:choose>
                     <xsl:when test="$VariantFroogleDescription!=''">
                        <xsl:value-of select="$VariantFroogleDescription" />
                     </xsl:when>
                     <xsl:when test="$ProductFroogleDescription!=''">
                        <xsl:value-of select="$ProductFroogleDescription" />
                     </xsl:when>
                     <xsl:when test="$VariantDescr!=''">
                        <xsl:value-of select="$VariantDescr" />
                     </xsl:when>
                     <xsl:when test="$ProductName!=''">
                        <xsl:value-of select="$ProductName" />
                     </xsl:when>
                     <xsl:otherwise>
                        <xsl:value-of select="$ProductDescr" />
                     </xsl:otherwise>
                  </xsl:choose>
               </description>
               <g:image_link>
                  <xsl:if test="not(contains($ItemImage, 'nopicture'))">
                     <xsl:value-of select="$ItemImage" />
                  </xsl:if>
               </g:image_link>
               <g:id>
                  <xsl:value-of select="$ProductVariantName"/>
               </g:id>
               <g:skusuffix>
                  <xsl:value-of select="$SkuSuffix"/>
               </g:skusuffix>
               <g:sku>
                  <xsl:value-of select="$Sku"/>
               </g:sku>
               <g:productid>
                  <xsl:value-of select="$ProductID" />
               </g:productid>
               <g:fullsku>
                  <xsl:value-of select="concat($pSKU, SkuSuffix)" />
               </g:fullsku>
               <!--<g:mpn>
                  <xsl:value-of select="$ManufacturerPartNumber"/>
                  </g:mpn>-->
               <g:brand>
                  <xsl:value-of select="$Brand"/>
               </g:brand>
               <g:price>
                  <!--<xsl:value-of select="number(price)" />-->            
                  <xsl:choose>
                     <xsl:when test="number(saleprice)=0">
                        <xsl:value-of select="format-number(price, '###0.00')" />
                     </xsl:when>
                     <xsl:otherwise>
                        <xsl:value-of select="format-number(saleprice, '###0.00')" />
                     </xsl:otherwise>
                  </xsl:choose>
               </g:price>
               <g:condition>
                  New
               </g:condition>
               <g:availability>in stock</g:availability>
               <xsl:value-of select="aspdnsf:SplitString(aspdnsf:GetMLValue(SEKeywords), ',', 'g:label')" disable-output-escaping="yes"  />
            </item>
         </xsl:template>
      </xsl:stylesheet>
   </PackageTransform>
</package>

 

What can I change in the above file so that I get the product SKU?

Thanks,

Matt

asked Oct 30, 2015 in MultiStore by Mden (310 points)
retagged Oct 30, 2015 by Mden

1 Answer

0 votes
The query that is being run on that XML package doesn't return the SkuSuffix field, so that's probably why you're not seeing it.

In order to add that field to the query results, add the following just before the word "from" in the query:

, pv.SkuSuffix
answered Nov 2, 2015 by Chris (3,685 points)
Thanks.

That works. I get the sku suffix which includes a ' ^'  before the number I actaully need.

What i really want is the [manufactuer part #] which wasn't working when i tried before but now it is.  I uncommented:

         <g:mpn>
            <xsl:value-of select="$ManufacturerPartNumber"/>
          </g:mpn>'
...