TA貢獻(xiàn)1810條經(jīng)驗(yàn) 獲得超4個(gè)贊
declare @T table(Roles xml)insert into @T values('<root>
<role>Alpha</role>
<role>Beta</role>
<role>Gamma</role>
</root>')declare @Role varchar(10)set @Role = 'Beta'select Rolesfrom @Twhere Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1如果您希望查詢能夠正常where col like '%Beta%'使用contains
declare @T table(Roles xml)insert into @T values('<root>
<role>Alpha</role>
<role>Beta</role>
<role>Gamma</role>
</root>')declare @Role varchar(10)set @Role = 'et'select Rolesfrom @Twhere Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1
TA貢獻(xiàn)1877條經(jīng)驗(yàn) 獲得超6個(gè)贊
如果您的字段名稱是角色，而表名是table1，您可以使用以下搜索
DECLARE @Role varchar(50);SELECT * FROM table1
WHERE Roles.exist ('/root/role = sql:variable("@Role")') = 1舉報(bào)