方式一:
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
');
方式二:
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")]');