Thread: Call SQL query from an XMLPackage

What would be the fastest way to NOT display a sub-Manufacturer if they dont have any items in inventory?

I created a seperate SQL query in my XMLPackage:

HTML Code:

<query name="Inv" rowElementName="Amount">
   <sql>
      <![CDATA[
         SELECT dbo.ProductVariant.Inventory
         FROM ProductVariant, ProductManufacturer WITH (nolock)
         WHERE dbo.ProductVariant.ProductID = dbo.ProductManufacturer.ProductID
         AND dbo.ProductManufacturer.ManufacturerID = @EntityID
      ]]>
   </sql>
   <queryparam paramname="@EntityID" paramtype="runtime"
      requestparamname="EntityID" sqlDataType="int" defvalue="0"  validationpattern="" />
</query>

then I try to call it from within:

HTML Code:

<!-- Display item number -->
<xsl:value-of select="/root/Inv/Amount" />

<!-- Do something with the number -->
<xsl:if test="count(/root/Inv/Amount) = 0">NO ITEMS</xsl:if>

I can get each sub-Manufacturer ID with this:

HTML Code:

<xsl:value-of select="EntityID" />

but how do I pass the ID to the query or even call it from within?

Originally the query will just get the EntityID from the MAIN TOP Manufacturer, but that's not what I need.
I need to call the query from within the code so then I can pass it the EntityID from the SUB-Manufacturer(s).

Thanks, _Brian

Never ever run the ieSpell.exe or ie, in that case, during a reply!

Anyway, after a little more research I found that <queryafter>, NOT <query>, will work after the XML is built here.

But how about passing the param to it? Just to at least see the item inventory number/amount.

I've tried:

Code:

<xsl:value-of select="/root/Inv/Amount" />

and:

Code:

<xsl:value-of select="/root/Inv/Amount[EntityID]" />

and:

Code:

<xsl:value-of select="/root/Inv/Amount[$EntityID]" />

Anyone want to take a crack at it cause I'm practically guessing at this point.
I also haven't found anything within the project to help as an example, at all...

Thanks, _Brian