SELECT node text values from xml document in TSQL OPENXML

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

DECLARE @idoc int  
DECLARE @doc varchar(1000)  
SET @doc ='
<products>    
 <rows>
  <row>
   <cell>1</cell>
   <cell>BALSAMO DERMOSCENT</cell>
   <cell>1.00</cell>
   <cell>开发者_如何学编程;0.00</cell>
   <cell>18.00</cell>
   <cell>18.00</cell>
   <cell>8.00</cell>
   <cell>427</cell>
   <cell>No</cell>
  </row>
  <row>
   <cell>2</cell>
   <cell>BAYTRIL 150 MG 1 CPDO</cell>
   <cell>1.00</cell>
   <cell>0.00</cell>
   <cell>3.50</cell>
   <cell>3.50</cell>
   <cell>8.00</cell>
   <cell>57</cell>
   <cell>No</cell>
  </row>
 </rows>
</products>'
--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()')

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:-

Prod       Description              Qty
---------- --------------------     --------
1          BALSAMO DERMOSCENT       1.00  
2          BAYTRIL 150 MG 1 CPDO    1.00

I'm using MSSQL 2008

---

I've come up with the following which does the job for me

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<products>
  <rows>
    <row>
      <cell>1</cell>
      <cell>BALSAMO DERMOSCENT</cell>
      <cell>1.00</cell>
      <cell>0.00</cell>
      <cell>18.00</cell>
      <cell>18.00</cell>
      <cell>8.00</cell>
      <cell>427</cell>
      <cell>No</cell>
    </row>
    <row>
      <cell>2</cell>
      <cell>BAYTRIL 150 MG 1 CPDO</cell>
      <cell>1.00</cell>
      <cell>0.00</cell>
      <cell>3.50</cell>
      <cell>3.50</cell>
      <cell>8.00</cell>
      <cell>57</cell>
      <cell>No</cell>
    </row>
  </rows>
</products>'
--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',1)
with (pLineNo int 'cell[1]/text()',
      pDesc varchar(50) 'cell[2]/text()',
      pQty float 'cell[3]/text()',
      pCost float 'cell[4]/text()',
      pPvp float 'cell[5]/text()',
      pTotal float 'cell[6]/text()',
      pIva float 'cell[7]/text()',
      pId int 'cell[8]/text()',
      pnoFact varchar(5) 'cell[9]/text()')

---

Why use openxml on sql server 2008?

This is a better option (I used varchar(max) as the datatype, but enter whatever is applicable). Note you have to declare the variable as xml, not varchar.

SELECT
 Row.Item.value('data(cell[1])', 'varchar(max)') As Prod,
 Row.Item.value('data(cell[2])', 'varchar(max)') As Description,
 Row.Item.value('data(cell[3])', 'varchar(max)') As Qty
FROM
 @doc.nodes('//row') AS Row(Item)

Note: If you're doing this is a stored procedure you may have to include the following before the select statement:

SET ARITHABORT ON -- required for .nodes

If you must use openxml, at least clean it up when you're done:

exec sp_xml_removedocument @idoc