declare @xml xml
set @xml='<root>
<item>
<items Id="aa" Name="abced"></items>
<items Id="aa" Name="ab"></items>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="def"></items>
</item>
<item>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="ed"></items>
<items Id="aa" Name="abc"></items>
<items Id="aa" Name="def"></items>
</item>
</root>'
如何查询出属性Name包含c的
set @xml='<root>
<item>
<items Id="aa" Name="abced"></items>
<items Id="aa" Name="ab"></items>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="def"></items>
</item>
<item>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="ed"></items>
<items Id="aa" Name="abc"></items>
<items Id="aa" Name="def"></items>
</item>
</root>'
如何查询出属性Name包含c的
declare @xml xml
set @xml='<root>
<item>
<items Id="aa" Name="abced"></items>
<items Id="aa" Name="ab"></items>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="def"></items>
</item>
<item>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="ed"></items>
<items Id="aa" Name="abc"></items>
<items Id="aa" Name="def"></items>
</item>
</root>'
--
--select @xml.value('(/root/item/items/@Name)[1]','nvarchar(max)') as name
--union all
--select @xml.value('(/root/item/items/@Name)[2]','nvarchar(max)')
--union all
--select @xml.value('(/root/item/items/@Name)[3]','nvarchar(max)')
--union all
--select @xml.value('(/root/item/items/@Name)[4]','nvarchar(max)')
--union all
--select @xml.value('(/root/item/items[2]/@Name)[1]','nvarchar(max)')
--union all
--select @xml.value('(/root/item/items[2]/@Name)[2]','nvarchar(max)')
--union all
--select @xml.value('(/root/item/items[2]/@Name)[3]','nvarchar(max)')
--union all
--select @xml.value('(/root/item/items[2]/@Name)[4]','nvarchar(max)')
--select T.c.query('item') as result from @xml.nodes('/root/item') as T(c)
select name
from (
select @xml.value('(/root/item/items[1]/@Name)[1]','nvarchar(max)') as name
union all
select @xml.value('(/root/item/items[1]/@Name)[2]','nvarchar(max)')
union all
select @xml.value('(/root/item/items[1]/@Name)[3]','nvarchar(max)')
union all
select @xml.value('(/root/item/items[1]/@Name)[4]','nvarchar(max)')
union all
select @xml.value('(/root/item/items[2]/@Name)[1]','nvarchar(max)')
union all
select @xml.value('(/root/item/items[2]/@Name)[2]','nvarchar(max)')
union all
select @xml.value('(/root/item/items[2]/@Name)[3]','nvarchar(max)')
union all
select @xml.value('(/root/item/items[2]/@Name)[4]','nvarchar(max)') ) a
where a.name like '%c%'
declare @x xml;
set @x=
'<root>
<item>
<items Id="aa" Name="abced"></items>
<items Id="aa" Name="ab"></items>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="def"></items>
</item>
<item>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="ed"></items>
<items Id="aa" Name="abc"></items>
<items Id="aa" Name="def"></items>
</item>
</root>';
select @x.query('
for $r in /root/*
for $i in $r/*
where contains(string($i/@Name),"c")
return $i
');
/*
<items Id="aa" Name="abced" />
<items Id="aa" Name="ce" />
<items Id="aa" Name="ce" />
<items Id="aa" Name="abc" />
*/
declare @x xml;
set @x=
'<root>
<item>
<items Id="aa" Name="abced"></items>
<items Id="aa" Name="ab"></items>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="def"></items>
</item>
<item>
<items Id="aa" Name="ce"></items>
<items Id="aa" Name="ed"></items>
<items Id="aa" Name="abc"></items>
<items Id="aa" Name="def"></items>
</item>
</root>';
select @x.query('/root/item/items[contains(string(./@Name),"c")]');