XML之sql:column用法对性能影响

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

--Sql:column

--元素
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"/>'


--方法1
SELECT 
	b.*
FROM 
	(SELECT ID =1) AS a
CROSS APPLY
(SELECT 
	T.c.value('(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[text()=sql:column("a.ID")]') T(c)
) AS b

--方法2

SELECT 
	b.*
FROM 
	(SELECT ID =1) AS a
CROSS APPLY
(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 @x.nodes('SO[ID=sql:column("a.ID")]') T(c)
) AS b

--方法3
SELECT 
	b.*
FROM 
	(SELECT ID =1) AS a
CROSS APPLY
(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:column("a.ID")]') T(c)
) AS b

--方法4:属性
SELECT 
	b.*
FROM 
	(SELECT ID =1) AS a
CROSS APPLY
(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:column("a.ID")]') T(c)
) AS b

 

查看执行计划:

sql:column+position用法:

 

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


SELECT 
	b.*
FROM 
	(SELECT ID =1 UNION ALL SELECT 2) AS a
CROSS APPLY
(SELECT 
	T.c.value('@ID','int')  AS ID,
	T.c.value('@SONr','varchar(50)')  AS SONr,
	T.c.value('@Customer','varchar(50)')  AS Customer,
	T.c.value('@OrderDate','datetime')  AS OrderDate
FROM @z.nodes('SO[position()=sql:column("a.ID")]') T(c)
) AS b
/*
ID	SONr	Customer	OrderDate
1	SO#1	Roy	2012-12-01 10:00:00.000
3	SO#1	Roy	2012-12-01 10:00:00.000
*/