[数据库] 请教一个关于sql2005中XML字段查询的问题

Question

现在有一张表dbo.xmltest，表中数据如下：
[dbo.xmltest]
id                data
1                <root><item id="18">aaaaa</item></root>
2                <root><item id="21">bbbbb</item><item id="34">bbbbb</item></root>
3                <root><item id="23">ccccc</item></root>
4                <root><item id="27">ddddd</item></root>
5                <root><item id="32">eeeee</item><item id="57">eeeee</item></root>
6                <root><item id="39">fffff</item></root>
7                <root><item id="43">ggggg</item><item id="45">ggggg</item></root>
8                <root><item id="46">hhhhh</item></root>
9                <root><item id="54">iiiii</item></root>
10              <root><item id="58">jjjjj</item></root>现在需要获取这张表中，data字段中所有item节点的id能被3整除的记录最后得到的记录如下所示：
id                data
1                <root><item id="18">aaaaa</item></root>
2                <root><item id="21">bbbbb</item><item id="34">bbbbb</item></root>
4                <root><item id="27">ddddd</item></root>
5                <root><item id="32">eeeee</item><item id="57">eeeee</item></root>
6                <root><item id="39">fffff</item></root>
7                <root><item id="43">ggggg</item><item id="45">ggggg</item></root>
9                <root><item id="54">iiiii</item></root>
请问，我现在该如何写sql语句呢？

Answer 1

go
create function [dbo].[get_number](@s varchar(100))
returns varchar(100)
as
begin
while patindex('%[^0-9]%',@s) > 0
begin
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
end
return @s
endgo
declare @table table (id int,data varchar(200))
insert into @table
select 1,'<root><item id="18">aaaaa</item></root>'union all
select 2,'<root><item id="21">bbbbb</item><item id="34">bbbbb</item></root>' union all
select 3,'<root><item id="23">ccccc</item></root>'union all
select 4,'<root><item id="27">ddddd</item></root>'union all
select 5,'<root><item id="32">eeeee</item><item id="57">eeeee</item></root>' union all
select 6,'<root><item id="39">fffff</item></root>'union all
select 7,'<root><item id="43">ggggg</item><item id="45">ggggg</item></root>' union all
select 8,'<root><item id="46">hhhhh</item></root>' union all
select 9,'<root><item id="54">iiiii</item></root>' union all
select 10,'<root><item id="58">jjjjj</item></root>'select * from @table where dbo.get_number(data)%3=0
/*
id          data
----------- --------------------------------------
1           <root><item id="18">aaaaa</item></root>
4           <root><item id="27">ddddd</item></root>
6           <root><item id="39">fffff</item></root>
9           <root><item id="54">iiiii</item></root>
*/

Answer 2

楼主给出的结果不对
既然是所有item节点的id能被3整除的记录
第2条的 id="34"
第5条的 id="32"
第7条的 id="43" 不满足条件

Answer 3

我可能没说清楚，我需的是返回  [size=5]data字段中所有item节点的id能被3整除的整条记录（当然也就包含id=34这些记录，因为它所在的xml中有一个item节点的id满足了被3整除的条件）[/size]

Answer 4

我可能没说清楚，我需的是返回  data字段中所有item节点的id能被3整除的整条记录（当然也就包含id=34这些记录，因为它所在的xml中有一个item节点的id满足了被3整除的条件）
我现在正在看楼上的代码

Answer 5

看看这个 
create table xmltest
(
id int identity (1,1 ) primary key,
data xml
)
 
go
insert into xmltest values('<root> <item id="18">aaaaa </item> </root>')
insert into xmltest values('<root> <item id="21">bbbbb </item> <item id="34">bbbbb </item> </root>')
insert into xmltest values('<root> <item id="23">ccccc </item> </root>')
insert into xmltest values('<root> <item id="27">ddddd </item> </root>')
insert into xmltest values('<root> <item id="32">eeeee </item> <item id="57">eeeee </item> </root>')
insert into xmltest values('<root> <item id="39">fffff </item> </root>')
insert into xmltest values('<root> <item id="43">ggggg </item> <item id="45">ggggg </item> </root>')
insert into xmltest values('<root> <item id="46">hhhhh </item> </root>')
insert into xmltest values('<root> <item id="54">iiiii </item> </root>')
insert into xmltest values('<root> <item id="58">jjjjj </item> </root>')go
select * from xmltest where data.value('(/root/item/@id)[1]','int')%3= 0 
union all
select * from xmltest where data.value('(/root/item/@id)[2]','int')%3= 0 
order by Id 

Answer 6

create table xmltest
(
id int identity (1,1 ) primary key,
data xml
)
 
go
insert into xmltest values('<root> <item id="18">aaaaa </item> </root>')
insert into xmltest values('<root> <item id="21">bbbbb </item> <item id="34">bbbbb </item> </root>')
insert into xmltest values('<root> <item id="23">ccccc </item> </root>')
insert into xmltest values('<root> <item id="27">ddddd </item> </root>')
insert into xmltest values('<root> <item id="32">eeeee </item> <item id="57">eeeee </item> </root>')
insert into xmltest values('<root> <item id="39">fffff </item> </root>')
insert into xmltest values('<root> <item id="43">ggggg </item> <item id="45">ggggg </item> </root>')
insert into xmltest values('<root> <item id="46">hhhhh </item> </root>')
insert into xmltest values('<root> <item id="54">iiiii </item> </root>')
insert into xmltest values('<root> <item id="58">jjjjj </item> </root>')SELECT *
FROM xmltest AS A
WHERE NOT EXISTS(
        SELECT 1
        FROM A.data.nodes('//item') AS T(x)
        WHERE T.x.value('@id','int') % 3 <>0
    ) 
DROP TABLE xmltest;

Answer 7

create table xmltest
(
id int identity (1,1 ) primary key,
data xml
)
 
go
insert into xmltest values('<root> <item id="18">aaaaa </item> </root>')
insert into xmltest values('<root> <item id="21">bbbbb </item> <item id="34">bbbbb </item> </root>')
insert into xmltest values('<root> <item id="23">ccccc </item> </root>')
insert into xmltest values('<root> <item id="27">ddddd </item> </root>')
insert into xmltest values('<root> <item id="32">eeeee </item> <item id="57">eeeee </item> </root>')
insert into xmltest values('<root> <item id="39">fffff </item> </root>')
insert into xmltest values('<root> <item id="43">ggggg </item> <item id="45">ggggg </item> </root>')
insert into xmltest values('<root> <item id="46">hhhhh </item> </root>')
insert into xmltest values('<root> <item id="54">iiiii </item> </root>')select * from xmltest 
where id not in (
select id from xmltest
where data.exist('root/item[@id mod 3 != 0]')=1
)drop table xmltest

Answer 8

不知这样行不行得通，msdn上查到的  http://msdn.microsoft.com/zh-cn/library/ms189869.aspx--sql2008
select * from xmltest
where xmldata.exist('/Classes/Class[(@id cast as xs:int) % 3 = 0]') = 1在2005下运行提示：
消息 9301，级别 16，状态 1，第 3 行
XQuery [xmltest.xmldata.exist()]: 在此版本的服务器中，'cast as <type>'不可用。请使用 'cast as <type> ?'语法。
看来只得晚上回去试试了

Answer 9

没用啊，在2008上试过了，还是一样的错误提示消息 9301，级别 16，状态 1，第 3 行 
XQuery [xmltest.xmldata.exist()]: 在此版本的服务器中，'cast as <type>'不可用。请使用 'cast as <type> ?'语法。