从 XQuery 和 SQL Server 2005 获取行集 [英] Getting rowsets from XQuery and SQL Server 2005

问题描述

我可以在 SQL Server 2005 中从下面的代码中取回第一条记录.如何全部取回?如果我删除 '[1]' 索引，我会得到一些单例错误...

I can get the first record back from the code below in SQL Server 2005. How do I get them all? If I remove the '[1]' index I get some singleton error...

declare @xml xml
set @xml = 
'<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-03-16T20:13:11">
<my:field>test1</my:field>
<my:field>test2</my:field>
<my:field>test3</my:field>
<my:field>test4</my:field>
</my:myFields>'
SELECT @xml.value('declare namespace     my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-03-16T20:13:11";         
(/my:myFields/my:field)[1]', 'varchar(100)') as test

推荐答案

declare @xml xml
set @xml = 
'<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-03-16T20:13:11">
<my:field>test1</my:field>
<my:field>test2</my:field>
<my:field>test3</my:field>
<my:field>test4</my:field>
</my:myFields>'

SELECT Y.ID.value('.', 'varchar(100)') as test
FROM @xml.nodes('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-03-16T20:13:11"; 
                (/my:myFields/my:field)') as Y(ID)

这篇关于从 XQuery 和 SQL Server 2005 获取行集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！