SQL Export ImageURL's and ImageFilenameOverrides

[joecalardo]

Looking to Export the image url's and the imagefilename overrides, some images I have not uploaded through the store front, some I had just bulk uploaded into my ftp and then used imagefilenameoverride with the filenames provided to me by my vendors data feed, couldnt keep up with 1000's of images. Anyway to do this properly ?

[BFG 9000]

Here's an xmlpackage which will do this :-

<?xml version="1.0" standalone="yes" ?>
<package version="2.1" displayname="BFG 9000 - All Product Images" debug="false" includeentityhelper="false" allowengine="true">

<query name="Products" rowElementName="Product">
<sql>
<![CDATA[
select ProductID, Name, SEName, SKU,ImageFilenameOverride
from product
where issystem=0 and deleted=0 and published=1
order by SKU
]]>
</sql>
</query>

<PackageTransform>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
<xsl:output method="html" omit-xml-declaration="yes" />

<xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" />

<xsl:template match="/">
<h1>Products without images</h1>
<table border="0" cellpadding="0" cellspacing="4" width="100%">
<xsl:apply-templates select="/root/Products" />
</table>

</xsl:template>

<xsl:template match="Products">
<tr class="DarkCell">
<td><font class="CondensedDarkCellText"><b>ProductID</b></font></td>
<td><font class="CondensedDarkCellText"><b>Product</b></font></td>
<td><font class="CondensedDarkCellText"><b>SKU</b></font></td>
<td><font class="CondensedDarkCellText"><b>ImageFilenameOverride</b></font></td>
<td><font class="CondensedDarkCellText"><b>Icon</b></font></td>
<td><font class="CondensedDarkCellText"><b>Medium</b></font></td>
<td><font class="CondensedDarkCellText"><b>Large</b></font></td>
</tr>
<xsl:apply-templates select="/root/Products/Product" />
</xsl:template>

<xsl:template match="Product">
<xsl:param name="pName" select="aspdnsf:GetMLValue(Name)" />
<xsl:param name="sename" select="aspdnsf:GetMLValue(SEName)" />
<xsl:param name="URL">p-<xsl:value-of select="ProductID" disable-output-escaping="yes" />-<xsl:value-of select="$sename"/>.aspx</xsl:param>
<xsl:param name="IconImageURL"><xsl:value-of select="aspdnsf:ProductImageUrl(ProductID, ImageFilenameOverride, SKU, 'icon', 0)"/></xsl:param>
<xsl:param name="MediumImageURL"><xsl:value-of select="aspdnsf:ProductImageUrl(ProductID, ImageFilenameOverride, SKU, 'medium', 0)"/></xsl:param>
<xsl:param name="LargeImageURL"><xsl:value-of select="aspdnsf:ProductImageUrl(ProductID, ImageFilenameOverride, SKU, 'large', 0)"/></xsl:param>
<tr>
<td><a href="{$URL}"><xsl:value-of select="ProductID" disable-output-escaping="yes"/></a></td>
<td><a href="{$URL}"><xsl:value-of select="$pName" disable-output-escaping="yes"/></a></td>
<td><xsl:value-of select="SKU" /></td>
<td align="center" valign="bottom"><xsl:value-of select="ImageFilenameOverride" /></td>
<td align="center" valign="bottom"><xsl:value-of select="$IconImageURL" /></td>
<td align="center" valign="bottom"><xsl:value-of select="$MediumImageURL" /></td>
<td align="center" valign="bottom"><xsl:value-of select="$LargeImageURL" /></td>
</tr>
</xsl:template>

</xsl:stylesheet>
</PackageTransform>
</package>

Save that lot as all.images.xml.config in your xmlpackages directory & then run it at http://www.yoursite.com/x-all.images.aspx


It's then trivial to pull it into excel or similar...



TTFN

BFG

[cengen]

thanks BFG9000 !

I needed something like this, and found this thread.

One minor point that might throw a curve ball towards some people...

Save the file all.images.xml.config (not all-images.xml.config)

[BFG 9000]

Good spot - Thanks.

I've edited the instructions!



TTFN

BFG