sql对xml的模糊查询

方式一:

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")]
  
  '
  
  );