XML之sql:variable性能比较

USE tempdb
GO
--Xml采用元素时，Xml文件比较小，用属性解析速度会相关较，通过查看执行计划可以，通过Openxml先分析比较，性能等同


--元素
DECLARE @x XML
SET @x=
'<SO>
	<ID>1</ID>
	<SONr>SO#1</SONr>
	<Customer>Roy</Customer>
	<OrderDate>2012-12-01 10:00</OrderDate>
</SO>'

--属性
DECLARE @y XML 
SET @y=
'<SO ID="1" SONr="SO#1" Customer="Roy" OrderDate="2012-12-01 10:00"/>'

DECLARE @idoc_x int
EXEC sp_xml_preparedocument @idoc_x OUTPUT,@x

DECLARE @idoc_y int
EXEC sp_xml_preparedocument @idoc_y OUTPUT,@y
--sql:variable



DECLARE @ID INT
SET @ID=1

--1、元素
SELECT 
	T.c.value('(ID/text())[1]','int')  AS ID,
	T.c.value('(SONr/text())[1]','varchar(50)')  AS SONr,
	T.c.value('(Customer/text())[1]','varchar(50)')  AS Customer,
	T.c.value('(OrderDate/text())[1]','datetime')  AS OrderDate
FROM @x.nodes('SO[ID=sql:variable("@ID")]') T(c)

--2、用Openxml读元素
SELECT 
	* 
FROM OPENXML(@idoc_x,'SO[ID=sql:variable("@ID")]',2) 
WITH(
	ID INT 'ID',
	SONr varchar(50) 'SONr',
	Customer varchar(50) 'Customer',
	OrderDate DATETIME 'OrderDate'
)


--3、属性
SELECT 
	T.c.value('@ID[1]','int')  AS ID,
	T.c.value('@SONr[1]','varchar(50)')  AS SONr,
	T.c.value('@Customer[1]','varchar(50)')  AS Customer,
	T.c.value('@OrderDate[1]','datetime')  AS OrderDate
FROM @y.nodes('SO[@ID=sql:variable("@ID")]') T(c)

--4、用Openxml读属性

SELECT 
	* 
FROM OPENXML(@idoc_y,'SO[ID=sql:variable("@ID")]',2) 
WITH(
	ID INT '@ID',
	SONr varchar(50) '@SONr',
	Customer varchar(50) '@Customer',
	OrderDate DATETIME '@OrderDate'
)



EXEC sp_xml_removedocument @idoc_x;
EXEC sp_xml_removedocument @idoc_y;

查看执行计划：