SELECT node text values from xml document in TSQL OPENXML

Question

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

caroig · Accepted Answer · 2010-12-15 13:53:24Z

6

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()')

answered Dec 15, 2010 at 13:53

caroig

1011 gold badge1 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mutation Person Over a year ago

Was curious about that myself. +1. Why not go ahead and accept your own answer ;-)

ScottE · Accepted Answer · 2010-12-15 14:13:41Z

0

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

answered Dec 15, 2010 at 14:13

ScottE

21.6k19 gold badges97 silver badges131 bronze badges

2 Comments

Mr Moose Over a year ago

What is the reason for the requirement of SET ARITHABORT ON? Do you have any reference material that indicates what the consequences are of not applying this?

ScottE Over a year ago

@MrMoose, the query wouldn't run otherwise. This would have been sql server 2005 at the time.

Collectives™ on Stack Overflow

SELECT node text values from xml document in TSQL OPENXML

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related