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
*/