OK so I've done this - it's ugly but it gets the job done :-
in My product xmlpackage I call a new xmlpackage like so :-
C#/VB.NET Code:
<xsl:value-of select="aspdnsf:XmlPackage('xmltest2.xml.config', concat('related=', $RelatedProducts, '&cat1=', $CAT))" disable-output-escaping="yes" />
$RelatedProducts is the value of the RelatedProducts field
&
$CAT is the Category we're viewing the product in.
The new xmlpackage looks like this :-
C#/VB.NET Code:
<package version="2.1" displayname="BFG merge related prods" debug="false" includeentityhelper="false" allowengine="true">
<query name="Related" rowElementName="Products">
<sql>
<![CDATA[
DECLARE @BFG varchar(600)
SET @BFG =
'SELECT TOP 3 ProductID FROM Product WHERE ProductID IN (' + @related + ') AND Deleted = 0
and Published = 1 ORDER BY newid()'
EXEC(@BFG )
]]>
</sql>
<queryparam paramname="@related" paramtype="runtime" requestparamname="related" sqlDataType="varchar" defvalue="0" validationpattern="" />
</query>
<query name="CategoryRandom" rowElementName="Products">
<sql>
<![CDATA[
SELECT TOP 3
p.ProductID
FROM Product p
join [dbo].ProductCategory pc with (nolock) on pc.productid = p.productid
WHERE p.Deleted = 0
and p.Published = 1 and pc.CategoryID = @cat1
ORDER BY newid()
]]>
</sql>
<queryparam paramname="@cat1" paramtype="runtime" requestparamname="cat1" 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" exclude-result-prefixes="aspdnsf">
<xsl:output method="xml" omit-xml-declaration="yes" />
<xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" />
<xsl:template match="/">
<xsl:param name="related"><xsl:apply-templates select="/root/Related/Products" /><xsl:if test="/root/Related/Products">,</xsl:if><xsl:apply-templates select="/root/CategoryRandom/Products" /></xsl:param>
<xsl:value-of select="aspdnsf:XmlPackage('three.random-new.xml.config', concat('related=', $related))" disable-output-escaping="yes" />
</xsl:template>
<xsl:template match="/root/Related/Products"><xsl:value-of select="concat(., substring(',', 1 div not(position()=last())))"/></xsl:template>
<xsl:template match="/root/CategoryRandom/Products"><xsl:value-of select="concat(., substring(',', 1 div not(position()=last())))"/></xsl:template>
</xsl:stylesheet>
</PackageTransform>
</package>
You can see that I then call three.random-new.xml.config which pulls the top 3 like this :-
C#/VB.NET Code:
<sql>
<![CDATA[
DECLARE @BFG varchar(600)
SET @BFG =
'SELECT DISTINCT TOP 3 p.ProductID, pv.Inventory FROM Product p LEFT JOIN ProductVariant pv on p.ProductID = pv.ProductID WHERE p.ProductID IN (' + @related + ') AND p.Published = 1 AND pv.Inventory > 0 '
EXEC(@BFG )
]]>
</sql>
TTFN
BFG