27,579
社区成员
发帖
与我相关
我的任务
分享
<dv>
<date>2011-05-01</date>
<value1>1</value1>
<value2>2</value2>
<value3>3</value3>
<value4>4</value4>
<value5>5</value5>
<value6>6</value6>
<value7>7</value7>
</dv>
<dv>
<date>2011-05-02</date>
<value1>1</value1>
<value2>2</value2>
<value3>3</value3>
<value4>4</value4>
<value5>5</value5>
<value6>6</value6>
<value7>7</value7>
</dv>
declare @a xml
set @a='
<ShoppingCart>
<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID="4523" CustomerID="2398"/>
<Purchase ProductID="8" Price="25.00" SaleDate="10/11/2007" SaleBatchID="4522" CustomerID="2344"/>
</ShoppingCart>'
declare @aa varchar(100)
set @aa='/ShoppingCart[1]/Purchase[1]/@ProductID'
print @aa
DECLARE @doc xml
SET @doc ='<?xml version="1.0" encoding="gb2312" ?>
<employees>
<employee>
<empid>e0001</empid>
<name>萧峰</name>
</employee>
<employee>
<empid>e0002</empid>
<name>段誉</name>
</employee>
<employee>
<empid>e0003</empid>
<name>王语嫣</name>
</employee>
<employee>
<empid>e0003</empid>
<name>张无忌</name>
</employee>
</employees>
'
create table people2
(
personid varchar(10) primary key ,
name varchar(20)
)
INSERT people2
SELECT DISTINCT b.* FROM
(SELECT x = @doc.query('for $e in //employee return //employee[empid = $e/empid][last()]')) a --FLWOR时,用当前节点去//emploee节点集中找节点集中empid等于当前节点的empid, 在找到的集合中取最后一个利用last()函数
CROSS APPLY
(SELECT id=t.x.value('empid[1]','varchar(100)'),name=t.x.value('name[1]','varchar(100)') FROM a.x.nodes('//employee') AS t(x)) b
SELECT * FROM people2
/*
e0001 萧峰
e0002 段誉
e0003 张无忌
*/
GO