我有这个xml:
DECLARE @x XML SET @x = '<data> <add>a</add> <add>b</add> <add>c</add> </data>';
任务:
我想列出a,b,c.
方法1:
SELECT s.value('.','VARCHAR(8000)') AS [ADD] FROM @x.nodes('/data/add') AS t(s)
方法2:
DECLARE @idoc INT EXEC sp_xml_preparedocument @idoc OUTPUT,@x SELECT * FROM OPENXML(@idoc,'/data/add',2) WITH ([add] NVARCHAR(MAX) '.')
他们都给我:
题 :
哪个是首选方式?
后者与前者(或副经文)有什么优势吗?
解决方法
一个简单的测试表明,您的方法1比方法2花费的时间更少.我不会总结这样的结论.它可以依赖于XML的结构以及您如何查询XML.
存储程序测试:
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
测试:
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
结果方法1:
sql Server Execution Times: cpu time = 63 ms,elapsed time = 70 ms.
结果方法2:
sql Server Execution Times: cpu time = 156 ms,elapsed time = 159 ms.
(在sql Server 2005上测试.)