如何查询SQL Server XML列中的值?

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

我在SQL Server数据库中存储了XML列(称为角色)中的XML。

<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.

我想列出所有具有特定角色的行。这个角色通过参数传递。

6 个解决方案

#1

151

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

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

这些页面将向您展示如何在T-SQL中查询XML:

Querying XML fields using t-sql

使用t-sql查询XML字段。

Flattening XML Data in SQL Server

将XML数据压缩到SQL Server中。

EDIT

编辑

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...

在玩了一会儿之后，我得到了一个使用交叉应用的令人惊奇的查询。这个将搜索每一行(角色)，以获得您所输入的like表达式中的值……

Given this table structure:

鉴于这种表结构:

create table MyTable (Roles XML)

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

We can query it like this:

我们可以这样查询:

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%'

You can check the SQL Fiddle here: http://sqlfiddle.com/#!3/ae0d5/13

您可以在这里查看SQL Fiddle: http://sqlfiddle.com/#!3/ae0d5/13。

#2

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 Roles
from @T
where Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1

If you want the query to work as where col like '%Beta%' you can use contains

如果您希望查询工作，可以使用包含“%Beta%”的col。

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 Roles
from @T
where Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1

#3

if your field name is Roles and table name is table1 you can use following to search

如果您的字段名是角色，表名是table1，您可以使用以下搜索。

DECLARE @Role varchar(50);
SELECT * FROM table1
WHERE Roles.exist ('/root/role = sql:variable("@Role")') = 1

#4

I came up with a simple work around below which is easy to remember too :-)

我想出了一个简单的工作在下面，这也很容易记住:-)

select * from  
(select cast (xmlCol as varchar(max)) texty
 from myTable (NOLOCK) 
) a 
where texty like '%MySearchText%'

#5

You could do the following

你可以这样做。

declare @role varchar(100) = 'Alpha'
select * from xmltable where convert(varchar(max),xmlfield) like '%<role>'+@role+'</role>%'

Obviously this is a bit of a hack and I wouldn't recommend it for any formal solutions. However I find this technique very useful when doing adhoc queries on XML columns in SQL Server Management Studio for SQL Server 2012.

显然，这是一种黑客行为，我不会推荐任何正式的解决方案。但是，我发现这种技术在SQL Server Management Studio的SQL Server Management Studio中对XML列进行临时查询时非常有用。

#6

I used the below statement to retrieve the values in the XML in the Sql table

我使用下面的语句在Sql表中检索XML中的值。

with xmlnamespaces(default 'http://test.com/2008/06/23/HL.OnlineContract.ValueObjects')
select * from (
select
            OnlineContractID,
            DistributorID,
            SponsorID,
    [RequestXML].value(N'/OnlineContractDS[1]/Properties[1]/Name[1]', 'nvarchar(30)') as [Name]
   ,[RequestXML].value(N'/OnlineContractDS[1]/Properties[1]/Value[1]', 'nvarchar(30)') as [Value]
     ,[RequestXML].value(N'/OnlineContractDS[1]/Locale[1]', 'nvarchar(30)') as [Locale]
from [OnlineContract]) as olc
where olc.Name like '%EMAIL%' and olc.Value like '%EMAIL%' and olc.Locale='UK EN'

#1

151

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