22,210
社区成员
发帖
与我相关
我的任务
分享
<Person>
<Node>
<Name>admin</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>lisi</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>wangwu</Name>
<Date>2010-4-7</Date>
</Node>
</Person>
--last()
SELECT
A.x.value('(Node/Name)[last()]','varchar(10)') Name1
FROM @xml.nodes('Person') AS A(x)
declare @xml xml
set @xml='
<Person>
<Node>
<Name>admin</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>lisi</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>wangwu</Name>
<Date>2010-4-7</Date>
</Node>
</Person>'
SELECT
A.x.value('(Node/Name)[1]','varchar(10)') Name1,
A.x.value('(Node/Name)[2]','varchar(10)') Name2,
A.x.value('(Node/Name)[3]','varchar(10)') Name3
FROM @xml.nodes('Person') AS A(x)
/*
Name1 Name2 Name3
---------- ---------- ----------
admin lisi wangwu
(1 行受影响)
*/
还是要这样的结果?declare @xml xml
set @xml='
<Person>
<Node>
<Name>admin</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>lisi</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>wangwu</Name>
<Date>2010-4-7</Date>
</Node>
</Person>'
;with t as
(
SELECT
A.x.value('Name[1]','varchar(10)') Name
FROM @xml.nodes('//Node') AS A(x)
)
select STUFF((select ','+name from t for xml path('')),1,1,'')
/*
admin,lisi,wangwu
(1 行受影响)
*/