如何查询 SQL Server XML 列中的值 [英] How can I query a value in SQL Server XML column

问题描述

我将以下 XML 存储在 SQL Server 数据库的 XML 列(称为 Roles)中.

I have following XML stored in a XML column (called Roles) in a SQL Server database.

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

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

I'd like to list all rows that have a specific role in them. This role passed by parameter.

推荐答案

select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

如果您的列不是 XML，则需要对其进行转换.您还可以使用其他语法来查询 XML 数据的某些属性.这是一个例子...

In case your column is not XML, you need to convert it. You can also use other syntax to query certain attributes of your XML data. Here is an example...

假设数据列有这个:

<Utilities.CodeSystems.CodeSystemCodes iid="107" CodeSystem="2" Code="0001F" CodeTags="-19-"..../>

... 并且您只想要 CodeSystem = 2 的那些，那么您的查询将是:

... and you only want the ones where CodeSystem = 2 then your query will be:

select 
  [data] 
from
  [dbo].[CodeSystemCodes_data]
  
where
  CAST([data] as XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

这些页面将向您展示有关如何在 T-SQL 中查询 XML 的更多信息:

These pages will show you more about how to query XML in T-SQL:

使用 t-sql 查询 XML 字段

在 SQL Server 中展平 XML 数据

编辑

在玩了一会儿之后，我最终得到了这个使用 交叉申请.这将在每一行(角色)中搜索您放入 like 表达式中的值...

After playing with it a little bit more, I ended up with this amazing query that uses CROSS APPLY. This one will search every row (role) for the value you put in your like expression...

鉴于此表结构:

create table MyTable (Roles XML)

insert into MyTable values
('<root>
   <role>Alpha</role>
   <role>Gamma</role>
   <role>Beta</role>
</root>')

我们可以这样查询:

select * from 

(select 
       pref.value('(text())[1]', 'varchar(32)') as RoleName
from 
       MyTable CROSS APPLY

       Roles.nodes('/root/role') AS Roles(pref)
)  as Result

where RoleName like '%ga%'

您可以在此处查看 SQL Fiddle:http://sqlfiddle.com/#!18/dc4d2/1/0

You can check the SQL Fiddle here: http://sqlfiddle.com/#!18/dc4d2/1/0

这篇关于如何查询 SQL Server XML 列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！