title: SELECT node text values from xml document in TSQL OPENXML
body: <p>I have a xml document I want to use to update values in a stored procedure.  I can process the XML using OPENXML, but I'm confused about extracting the values I want.  Each row in the xml is a product record and I want to create a variable for each property.  Cell0 is the ID, Cell2 description etc</p>
  
  <pre><code>DECLARE @idoc int  
  DECLARE @doc varchar(1000)  
  SET @doc ='
  &lt;products&gt;    
   &lt;rows&gt;
    &lt;row&gt;
     &lt;cell&gt;1&lt;/cell&gt;
     &lt;cell&gt;BALSAMO DERMOSCENT&lt;/cell&gt;
     &lt;cell&gt;1.00&lt;/cell&gt;
     &lt;cell&gt;0.00&lt;/cell&gt;
     &lt;cell&gt;18.00&lt;/cell&gt;
     &lt;cell&gt;18.00&lt;/cell&gt;
     &lt;cell&gt;8.00&lt;/cell&gt;
     &lt;cell&gt;427&lt;/cell&gt;
     &lt;cell&gt;No&lt;/cell&gt;
    &lt;/row&gt;
    &lt;row&gt;
     &lt;cell&gt;2&lt;/cell&gt;
     &lt;cell&gt;BAYTRIL 150 MG 1 CPDO&lt;/cell&gt;
     &lt;cell&gt;1.00&lt;/cell&gt;
     &lt;cell&gt;0.00&lt;/cell&gt;
     &lt;cell&gt;3.50&lt;/cell&gt;
     &lt;cell&gt;3.50&lt;/cell&gt;
     &lt;cell&gt;8.00&lt;/cell&gt;
     &lt;cell&gt;57&lt;/cell&gt;
     &lt;cell&gt;No&lt;/cell&gt;
    &lt;/row&gt;
   &lt;/rows&gt;
  &lt;/products&gt;'
  --Create an internal representation of the XML document.
  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
  -- Execute a SELECT statement that uses the OPENXML rowset provider.
  SELECT    *
  FROM       OPENXML (@idoc, '/products/rows/row/cell',1)
  with (Col1 varchar(29) 'text()')
  </code></pre>
  
  <p>Running the above query returns 1 record for each CELL in the xml.  I want to be able to return 1 record per row with different columns for each cell, something like:-</p>
  
  <pre><code>Prod       Description              Qty
  ---------- --------------------     --------
  1          BALSAMO DERMOSCENT       1.00  
  2          BAYTRIL 150 MG 1 CPDO    1.00
  </code></pre>
  
  <p>I'm using MSSQL 2008</p>