SQL Server: OPENXML vs SELECT..FROM when dealing with XML?

Question

I have this xml :

DECLARE @x XML
SET @x = 
    '<data>
       <add>a</add>
       <add>b</add>
       <add>c</add>
     </data>';

Task :

I want to list the a,b,c.

approach 1 :

SELECT s.value('.', 'VARCHAR(8000)') AS [ADD]
FROM   @x.nodes('/data/add') AS t(s) 

approach 2:

DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @x

SELECT *
FROM   OPENXML(@idoc, '/data/add', 2)
       WITH ([add] NVARCHAR(MAX) '.')

both of them give me :

question :

which is the preferred way ?

Is there any advantages of the latter vs former ( or vice verse) ?

Answer 1

A simple test shows that your approach 1 takes less time than approach 2. I would not draw any conclusions about it always being the case. It can depend on how your XML is structured and how you need to query the XML.

Stored procedures to test on:

create procedure TestXML
  @X xml
as
set nocount on

select X.N.value('.', 'varchar(8000)')
from @X.nodes('/root/item') as X(N)

go

create procedure TestOpenXML
  @X xml
as
set nocount on

declare @idoc int
exec sp_xml_preparedocument @idoc out, @X

select value
from openxml(@idoc, '/root/item',1) 
  with (value  varchar(8000) '.')

exec sp_xml_removedocument @idoc

Test:

declare @X xml

set @X =
  (
    select number as '*'
    from master..spt_values
    for xml path('item'), root('root'), type
  )

set statistics time on
exec TestXML @X
exec TestOpenXML @X

Result approach 1:

SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 70 ms.

Result approach 2:

SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 159 ms.

(Tested on SQL Server 2005.)

Answer 2

I prefer #2. try the execution plan and see that the first approch costs 97% where as the cond one costs only 3%