如何查询SQL Server XML列中的值

Question

如何查询SQL Server XML列中的值

我跟踪存储在RolesSQL Server数据库中的XML列（称为）中的XML 。

<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role></root>

我想列出在其中具有特定角色的所有行。此角色由参数传递。

Answer 1

declare&nbsp;@T&nbsp;table(Roles&nbsp;xml)insert&nbsp;into&nbsp;@T&nbsp;values('<root> &nbsp;&nbsp;&nbsp;<role>Alpha</role> &nbsp;&nbsp;&nbsp;<role>Beta</role> &nbsp;&nbsp;&nbsp;<role>Gamma</role> </root>')declare&nbsp;@Role&nbsp;varchar(10)set&nbsp;@Role&nbsp;=&nbsp;'Beta'select&nbsp;Rolesfrom&nbsp;@Twhere&nbsp;Roles.exist('/root/role/text()[.&nbsp;=&nbsp;sql:variable("@Role")]')&nbsp;=&nbsp;1如果您希望查询能够正常where col like '%Beta%'使用containsdeclare&nbsp;@T&nbsp;table(Roles&nbsp;xml)insert&nbsp;into&nbsp;@T&nbsp;values('<root> &nbsp;&nbsp;&nbsp;<role>Alpha</role> &nbsp;&nbsp;&nbsp;<role>Beta</role> &nbsp;&nbsp;&nbsp;<role>Gamma</role> </root>')declare&nbsp;@Role&nbsp;varchar(10)set&nbsp;@Role&nbsp;=&nbsp;'et'select&nbsp;Rolesfrom&nbsp;@Twhere&nbsp;Roles.exist('/root/role/text()[contains(.,&nbsp;sql:variable("@Role"))]')&nbsp;=&nbsp;1

Answer 2

如果您的字段名称是角色，而表名是table1，您可以使用以下搜索DECLARE&nbsp;@Role&nbsp;varchar(50);SELECT&nbsp;*&nbsp;FROM&nbsp;table1 WHERE&nbsp;Roles.exist&nbsp;('/root/role&nbsp;=&nbsp;sql:variable("@Role")')&nbsp;=&nbsp;1