Royi Namir Royi Namir - 1 year ago 138
SQL Question

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

I have this xml :

SET @x =

Task :

I want to list the

approach 1 :

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

approach 2:

EXEC sp_xml_preparedocument @idoc OUTPUT, @x

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

both of them give me :

enter image description here

question :

which is the preferred way ?

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

Answer Source

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
set nocount on

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


create procedure TestOpenXML
  @X xml
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


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.)