Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

Discuss: XML to Table

Discussion for XML to Table.
2008/09/22 16:00 by vanthoog
Alternative approach
You can also achieve this using OPENXML. The following example produces exactly the same results as example 1 in the original post:


SET @xml = ' <root> <element id="1" item="evident" /> <element id="2" item="interactive" /> <element id="3" item="utrecht" /> </root> '

DECLARE @abtXmlHandle INT

EXEC sp_xml_preparedocument @abtXmlHandle OUTPUT, @xml

SELECT * FROM OPENXML(@abtXmlHandle , '/root/element' , 0) WITH ( Id INT '@id', Item NVARCHAR(32) '@item' )

A subtle difference is that when using OPENXML the input xml-document needs a root element.

Is there a performance difference between the original approach and the OPENXML approach? To investigate this I have performed a little test using a xml-document containing 40000 elements. The original approach takes ± 2 seconds and the OPENXML approach takes ±4 seconds. So, from a performance point of view the original approach is better/faster.

 © Evident Interactive BV