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;
查看执行计划: