Displaying a Stock Table for Matrix Items in Interprise 5.6.22

by jasonrshaver 8. September 2011 00:31

Some of the products we list have 18-24 permeations and we want to show customers how many we might have in stock currently.  Luckily with XmlPackages, this is easy to do.

First, let’s look at the result:

image

Setting Up the Database

The first step to our result is to get the current stock information into our XmlPackage via a Query.  There are two way to do this, rowsets and XML.  A rowset is the common ‘table’ of information of from the database.  This is how most other packages work.  When looking for help on using XML result sets for Interprise, I doubt there are any existing samples, so I figured I might as well be first. 

** Warning ** This method is complex and may not be the “best” method, but it is the one I went with.

First, how do we get SQL to produce well structured XML data.  Well, if you are rocking SQL Server 2005 or latter and understand the T-SQL syntax, this is simple with the ‘FOR XML’ keywords.  Here is my stored procedure for matrix items using up to 3 attributes (Sorry for the scroll bars, but it is crazy long):

  1. CREATE PROCEDURE [dbo].[ttx_GetItemInventoryXml]
  2.     @ItemCode nvarchar(30)
  3. AS
  4. BEGIN
  5.     SET NOCOUNT ON;
  6.  
  7.     DECLARE @AttributeCount int = 1
  8.     DECLARE @Result nvarchar(max)
  9.  
  10.     SELECT @AttributeCount = COUNT(*)
  11.         FROM InventoryItem i WITH (NOLOCK)
  12.         INNER JOIN InventoryAttribute ia WITH (NOLOCK) ON ia.ItemCode = i.ItemCode
  13.         WHERE i.ItemCode = @ItemCode
  14.  
  15.     IF (@AttributeCount = 0) BEGIN
  16.         SELECT '' -- This is built for Matrix items only right now
  17.     END
  18.  
  19.     IF (@AttributeCount = 1) BEGIN
  20.             DECLARE @MyTempTable1 TABLE
  21.             (
  22.                  Tag int
  23.                , Parent int
  24.                , [Item!1!ItemCode] nvarchar(30)
  25.                , [Attribute1!2!Attribute1] nvarchar(255)
  26.                , [Attribute1!2!AttributeCode1] nvarchar(255)
  27.                , [Attribute2!3] decimal
  28.             );
  29.  
  30.            insert into @MyTempTable1
  31.                 SELECT 1 AS 'Tag', NULL AS 'Parent'
  32.                      , imi.ItemCode AS [Item!1!ItemCode]
  33.                      , NULL AS [Attribute1!2!Attribute1]
  34.                      , NULL AS [Attribute1!2!AttributeCode1]
  35.                      , NULL AS [Attribute2!2]               
  36.                   
  37.                   from InventoryItem ii WITH (NOLOCK)
  38.              LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  39.              LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  40.                  WHERE imi.ItemCode = @ItemCode
  41.  
  42.                  UNION
  43.                        
  44.                        SELECT 2 AS 'Tag', 1 AS 'Parent', imi.ItemCode
  45.                          , imi.Attribute1, imi.AttributeCode1
  46.                          , isv.UnitsInStock
  47.                       from InventoryItem ii WITH (NOLOCK)
  48.                  LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  49.                  LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  50.                      WHERE imi.ItemCode = @ItemCode
  51.                                        
  52.              ORDER BY [Item!1!ItemCode]
  53.                     , [Attribute1!2!Attribute1]
  54.                     , [Attribute1!2!AttributeCode1]
  55.             
  56.             SET @Result = (SELECT * FROM @MyTempTable1 FOR XML EXPLICIT)
  57.             SELECT @Result
  58.     END
  59.  
  60.     IF (@AttributeCount = 2) BEGIN
  61.  
  62.             DECLARE @MyTempTable2 TABLE
  63.             (
  64.                  Tag int
  65.                , Parent int
  66.                , [Item!1!ItemCode] nvarchar(30)
  67.                , [Attribute1!2!Attribute1] nvarchar(255)
  68.                , [Attribute1!2!AttributeCode1] nvarchar(255)
  69.                , [Attribute2!3!Attribute2] nvarchar(255)
  70.                , [Attribute2!3!AttributeCode2] nvarchar(255)
  71.                , [Attribute3!4] decimal
  72.             );
  73.  
  74.            insert into @MyTempTable2
  75.                 SELECT 1 AS 'Tag', NULL AS 'Parent'
  76.                      , imi.ItemCode AS [Item!1!ItemCode]
  77.                      , NULL AS [Attribute1!2!Attribute1]
  78.                      , NULL AS [Attribute1!2!AttributeCode1]
  79.                      , NULL AS [Attribute2!3!Attribute2]
  80.                      , NULL AS [Attribute2!3!AttributeCode2]
  81.                      , NULL AS [Attribute3!3]               
  82.                   
  83.                   from InventoryItem ii WITH (NOLOCK)
  84.              LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  85.              LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  86.                  WHERE imi.ItemCode = @ItemCode
  87.  
  88.                  UNION
  89.                        
  90.                        SELECT 2 AS 'Tag', 1 AS 'Parent', imi.ItemCode
  91.                          , imi.Attribute1, imi.AttributeCode1
  92.                          , NULL, NULL
  93.                          , NULL
  94.                       from InventoryItem ii WITH (NOLOCK)
  95.                  LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  96.                  LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  97.                      WHERE imi.ItemCode = @ItemCode
  98.                        
  99.                      UNION
  100.                                        
  101.                             SELECT 3 AS 'Tag', 2 AS 'Parent', imi.ItemCode
  102.                                  , imi.Attribute1, imi.AttributeCode1
  103.                                  , imi.Attribute2, imi.AttributeCode2
  104.                                  , isv.UnitsInStock
  105.                               from InventoryItem ii WITH (NOLOCK)
  106.                          LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  107.                          LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  108.                              WHERE imi.ItemCode = @ItemCode
  109.                                        
  110.              ORDER BY [Item!1!ItemCode]
  111.                     , [Attribute1!2!Attribute1]
  112.                     , [Attribute1!2!AttributeCode1]
  113.                     , [Attribute2!3!Attribute2]
  114.             
  115.             SET @Result = (SELECT * FROM @MyTempTable2 FOR XML EXPLICIT)
  116.             SELECT @Result
  117.     END
  118.  
  119.     IF (@AttributeCount = 3) BEGIN
  120.             
  121.             DECLARE @MyTempTable3 TABLE
  122.             (
  123.                  Tag int
  124.                , Parent int
  125.                , [Item!1!ItemCode] nvarchar(30)
  126.                , [Attribute1!2!Attribute1] nvarchar(255)
  127.                , [Attribute1!2!AttributeCode1] nvarchar(255)
  128.                , [Attribute2!3!Attribute2] nvarchar(255)
  129.                , [Attribute2!3!AttributeCode2] nvarchar(255)
  130.                , [Attribute3!4!Attribute3] nvarchar(255)
  131.                , [Attribute3!4!AttributeCode3] nvarchar(255)
  132.                , [Attribute3!4] decimal
  133.             );
  134.  
  135.            insert into @MyTempTable3
  136.                 SELECT 1 AS 'Tag', NULL AS 'Parent'
  137.                      , imi.ItemCode AS [Item!1!ItemCode]
  138.                      , NULL AS [Attribute1!2!Attribute1]
  139.                      , NULL AS [Attribute1!2!AttributeCode1]
  140.                      , NULL AS [Attribute2!3!Attribute2]
  141.                      , NULL AS [Attribute2!3!AttributeCode2]
  142.                      , NULL AS [Attribute3!4!Attribute3]
  143.                      , NULL AS [Attribute3!4!AttributeCode3]
  144.                      , NULL AS [Attribute3!4]               
  145.                   
  146.                   from InventoryItem ii WITH (NOLOCK)
  147.              LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  148.              LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  149.                  WHERE imi.ItemCode = @ItemCode
  150.  
  151.                  UNION
  152.                        
  153.                        SELECT 2 AS 'Tag', 1 AS 'Parent', imi.ItemCode
  154.                          , imi.Attribute1, imi.AttributeCode1
  155.                          , NULL, NULL
  156.                          , NULL, NULL
  157.                          , NULL
  158.                       from InventoryItem ii WITH (NOLOCK)
  159.                  LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  160.                  LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  161.                      WHERE imi.ItemCode = @ItemCode
  162.                        
  163.                      UNION
  164.                                        
  165.                             SELECT 3 AS 'Tag', 2 AS 'Parent', imi.ItemCode
  166.                                  , imi.Attribute1, imi.AttributeCode1
  167.                                  , imi.Attribute2, imi.AttributeCode2
  168.                                  , NULL, NULL
  169.                                  , NULL
  170.                               from InventoryItem ii WITH (NOLOCK)
  171.                          LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  172.                          LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  173.                              WHERE imi.ItemCode = @ItemCode
  174.                                        
  175.                              UNION
  176.                                     SELECT 4 AS 'Tag', 3 AS 'Parent', imi.ItemCode
  177.                                          , imi.Attribute1 , imi.AttributeCode1
  178.                                          , imi.Attribute2, imi.AttributeCode2
  179.                                          , imi.Attribute3, imi.AttributeCode3
  180.                                          , isv.UnitsInStock
  181.                                       from InventoryItem ii WITH (NOLOCK)
  182.                                  LEFT JOIN InventoryStockTotal isv WITH (NOLOCK) ON ii.ItemCode = isv.ItemCode
  183.                                  LEFT JOIN InventoryMatrixItem imi WITH (NOLOCK) ON ii.ItemCode = imi.MatrixItemCode
  184.                                      WHERE imi.ItemCode = @ItemCode
  185.              ORDER BY [Item!1!ItemCode]
  186.                     , [Attribute1!2!Attribute1]
  187.                     , [Attribute1!2!AttributeCode1]
  188.                     , [Attribute2!3!Attribute2]
  189.                     , [Attribute3!4!Attribute3]
  190.             
  191.             SET @Result = (SELECT * FROM @MyTempTable3 FOR XML EXPLICIT)
  192.             SELECT @Result
  193.     END
  194.     
  195.     -- We don't support more than 3 attributes, but it is very easy to extend
  196.     -- this by following the pattern above.
  197.     IF (@AttributeCount > 3) BEGIN
  198.         SELECT ''
  199.     END
  200. END

And the output is a single row with a single column that looks like the following:

  1. <Item ItemCode="ITEM-000001">
  2.   <Attribute1 Attribute1="Black" AttributeCode1="Color">
  3.     <Attribute2 Attribute2="33 deg" AttributeCode2="Hardness">
  4.       <Attribute3 Attribute3="1.8mm" AttributeCode3="Thickness">15.000000</Attribute3>
  5.       <Attribute3 Attribute3="2.0mm" AttributeCode3="Thickness">0.000000</Attribute3>
  6.       <Attribute3 Attribute3="2.3mm" AttributeCode3="Thickness">0.000000</Attribute3>
  7.     </Attribute2>
  8.     <Attribute2 Attribute2="37 deg" AttributeCode2="Hardness">
  9.       <Attribute3 Attribute3="1.8mm" AttributeCode3="Thickness">0.000000</Attribute3>
  10.       <Attribute3 Attribute3="2.0mm" AttributeCode3="Thickness">0.000000</Attribute3>
  11.       <Attribute3 Attribute3="2.3mm" AttributeCode3="Thickness">0.000000</Attribute3>
  12.     </Attribute2>
  13.     <Attribute2 Attribute2="41 deg" AttributeCode2="Hardness">
  14.       <Attribute3 Attribute3="1.8mm" AttributeCode3="Thickness">1.000000</Attribute3>
  15.       <Attribute3 Attribute3="2.0mm" AttributeCode3="Thickness">0.000000</Attribute3>
  16.       <Attribute3 Attribute3="2.3mm" AttributeCode3="Thickness">0.000000</Attribute3>
  17.     </Attribute2>
  18.   </Attribute1>
  19.   <Attribute1 Attribute1="Red" AttributeCode1="Color">
  20.     <Attribute2 Attribute2="33 deg" AttributeCode2="Hardness">
  21.       <Attribute3 Attribute3="1.8mm" AttributeCode3="Thickness">0.000000</Attribute3>
  22.       <Attribute3 Attribute3="2.0mm" AttributeCode3="Thickness">0.000000</Attribute3>
  23.       <Attribute3 Attribute3="2.3mm" AttributeCode3="Thickness">0.000000</Attribute3>
  24.     </Attribute2>
  25.     <Attribute2 Attribute2="37 deg" AttributeCode2="Hardness">
  26.       <Attribute3 Attribute3="1.8mm" AttributeCode3="Thickness">0.000000</Attribute3>
  27.       <Attribute3 Attribute3="2.0mm" AttributeCode3="Thickness">0.000000</Attribute3>
  28.       <Attribute3 Attribute3="2.3mm" AttributeCode3="Thickness">0.000000</Attribute3>
  29.     </Attribute2>
  30.     <Attribute2 Attribute2="41 deg" AttributeCode2="Hardness">
  31.       <Attribute3 Attribute3="1.8mm" AttributeCode3="Thickness">0.000000</Attribute3>
  32.       <Attribute3 Attribute3="2.0mm" AttributeCode3="Thickness">0.000000</Attribute3>
  33.       <Attribute3 Attribute3="2.3mm" AttributeCode3="Thickness">0.000000</Attribute3>
  34.     </Attribute2>
  35.   </Attribute1>
  36. </Item>

Updating our XmlPackage

Now, how do we use that block of xml in our XmlPackages?  Well first, I will be modifying XmlPackages\product.matrixproduct.xml.config. 

Make sure you backup your XmlPackages before changing them!  You have been warned. 

First, lets add our Query to the Package element right before our PackageTransform element:

  1. <query name="Inventory" rowElementName="Item" retType="xml">
  2.     <sql>
  3.         <![CDATA[
  4.             exec ttx_GetItemInventoryXml @ItemCode
  5.         ]]>
  6.     </sql>
  7.     <queryparam paramname="@ItemCode" paramtype="runtime" requestparamname="ItemCode" sqlDataType="nvarchar" defvalue="" validationpattern=""/>
  8. </query>

What this does is call our stored procedure passing in the ItemCode.  Make special note of the retType attribute, this is what tells the XmlPackage to view the result set as XML.

Now to use that XML data, here is a xsl:template showing how to do two common outputs, a list and a table.  The XSL is hardcoded to use 3 attributes, but you can easily modify it to use any number of attributes instead.  If anyone can find a better way to ‘layout’ the table without using 10 xsl:for-each elements, please let me know. 

  1. <xsl:template name="InventoryTable">
  2.     <div class="ttxStockTable">       
  3.         
  4.         <!-- Uncomment this for a simple listing of products -->
  5.         <!--<xsl:for-each select="/root/Inventory/Item/Attribute1">
  6.             <xsl:for-each select="Attribute2">
  7.                 <xsl:for-each select="Attribute3">
  8.                     <xsl:value-of select="../../@AttributeCode1"/>: <xsl:value-of select="../../@Attribute1"/>,
  9.                     <xsl:value-of select="../@AttributeCode2"/>: <xsl:value-of select="../@Attribute2"/>,
  10.                     <xsl:value-of select="@AttributeCode3"/>: <xsl:value-of select="@Attribute3"/>,
  11.                     <xsl:value-of select="." /><br/>
  12.                 </xsl:for-each>
  13.             </xsl:for-each>
  14.         </xsl:for-each>-->
  15.  
  16.         <!-- Uncomment this for a pretty table of products -->
  17.         <table>
  18.             <tbody>
  19.                 <xsl:for-each select="/root/Inventory/Item[1]">
  20.                     <tr>
  21.                         <th></th>
  22.                         <xsl:for-each select="Attribute1" >
  23.                             <th class="headerName">
  24.                                 <xsl:attribute name="colspan">
  25.                                     <xsl:value-of select="count(Attribute2)" />
  26.                                 </xsl:attribute>
  27.                                 <xsl:value-of select="@Attribute1" />
  28.                             </th>
  29.                         </xsl:for-each>
  30.                     </tr>
  31.                 </xsl:for-each>
  32.  
  33.                 <xsl:for-each select="/root/Inventory/Item[1]">
  34.                     <tr>
  35.                         <th></th>
  36.                         <xsl:for-each select="/root/Inventory/Item[1]/Attribute1">
  37.                             <xsl:for-each select="Attribute2" >
  38.                                 <th class="headerName">
  39.                                     <xsl:value-of select="@Attribute2" />
  40.                                 </th>
  41.                         </xsl:for-each>
  42.                     </xsl:for-each>
  43.                     </tr>
  44.                 </xsl:for-each>
  45.  
  46.                 <xsl:for-each select="/root/Inventory/Item[1]/Attribute1[1]/Attribute2[1]/Attribute3">
  47.                     <tr>
  48.                         <xsl:variable name="CurrentAtt3" select="@Attribute3" />
  49.                         <td class="sizeLabel">
  50.                             <xsl:value-of select="@Attribute3"/>
  51.                         </td>
  52.                         <xsl:for-each select="/root/Inventory/Item/Attribute1">
  53.                             <xsl:for-each select="Attribute2">
  54.                                 <xsl:for-each select="Attribute3[@Attribute3=$CurrentAtt3]">
  55.                                     <!-- Uncomment this to give exact stock levels instead of hints -->
  56.                                     <!--<td><xsl:value-of select="." /></td>-->
  57.                                     <xsl:choose>
  58.                                         <xsl:when test=". = 1">
  59.                                             <td class="StockHint StockHint_LowStock">
  60.                                                 Low Stock
  61.                                             </td>
  62.                                         </xsl:when>
  63.                                         <xsl:when test=". = 2">
  64.                                             <td class="StockHint StockHint_InStock">
  65.                                                 In Stock
  66.                                             </td>
  67.                                         </xsl:when>
  68.                                         <xsl:when test=". >= 3">
  69.                                             <td class="StockHint StockHint_InStock">
  70.                                                 In Stock
  71.                                             </td>
  72.                                         </xsl:when>
  73.                                         <xsl:otherwise>
  74.                                             <td class="notInStock">
  75.                                             </td>
  76.                                         </xsl:otherwise>
  77.                                     </xsl:choose>
  78.                                 </xsl:for-each>
  79.                             </xsl:for-each>
  80.                         </xsl:for-each>
  81.                     </tr>
  82.                 </xsl:for-each>
  83.             </tbody>
  84.         </table>
  85.     </div>
  86. </xsl:template>

And finally, lets call that template in our product.  I put my right after the description (Line ~130) like so:

  1. <xsl:value-of select="$pDescription" disable-output-escaping="yes"/>
  2. <xsl:call-template name="InventoryTable" />

So, that was not too hard, and I hope this gives everyone the courage to start doing awesome stuff with their XmlPackages.  There is an endless supply of data out there and you can access it all!

Tags:

Submit to DotNetKicks...
Permalink | Comments (0)

About the author

I am a software developer working for Microsoft in Redmond, WA.  In addition, my wife and I own TTXOnline, what is likely the 3rd largest table tennis store in the US.

Month List

Page List