如何在SQL Server XML列中查询值

Question

我有一个XML存储在SQL Server数据库的XML列（名为Roles）中。

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

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

- Bistro

Answer 1

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

如果你的列不是XML类型，你需要进行转换。你也可以使用其他语法来查询XML数据中的某些属性。以下是一个示例...

假设数据列包含如下内容：

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

如果你只想得到 CodeSystem = 2 的结果，则查询语句如下：

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

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

使用 t-sql 查询 XML 字段

在 SQL Server 中压缩 XML 数据

编辑

在进一步尝试后，我得到了这个神奇的查询，它使用CROSS APPLY。 这个查询将搜索每一行（角色）中您在 like 表达式中输入的值...

给定这个表结构：

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

- Leniel Maccaferri

---

它回答了我所有的问题，你在回答中的[1]是什么作用？ - Bistro

很好的答案，我投票支持它，但我想字符串应该是varchar。 - AaA

“@Bistro提出的关于[1]的问题非常好。它意味着你从XML中选择第一个角色值，这意味着这只适用于在样本XML中查找Alpha。如果你搜索Beta，它将无法找到该行。” - Mikael Eriksson

在我的情况下，我必须查询具有特定属性值的节点。这个答案是我的解决方案的线索。我只需要在属性值周围加上双引号即可。 - John N

获取 - "无法在 nvarchar(max) 上调用方法"。请问有什么建议吗？ - Abhilash V

Answer 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

如果您想让查询的效果像 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 Roles
from @T
where Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1

- Mikael Eriksson

Answer 3

如果你的字段名是 "Roles"，表名是 "table1"，那么可以使用以下方法进行搜索。

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

- AaA

---

这很好，有没有办法使用 like 进行搜索？例如 /root/role like .... - Bistro

使用.value('(/root/role)[1]', 'varchar(max)') like '%yourtext%'代替Leniel所解释的exists。 - AaA

你试过这个吗？它可以找到任何东西，无论你在“@Role”中输入了什么。 - Mikael Eriksson

Answer 4

我想出了一个简单的解决方法，下面是易于记忆的 workaround :-)

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

- Sagar

---

我们不应该通过字符串操作来搜索，因为那会导致搜索速度过慢。 - Malcolm Salvador

Answer 5

您可以执行以下操作：

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

显然，这是一种比较粗暴的方法，我不建议在任何正式的解决方案中使用。但是，当在SQL Server管理工具中对SQL Server 2012中的XML列进行临时查询时，我发现这种技巧非常有用。

- Craig B

Answer 6

有用的提示。在带命名空间的SQL Server XML列中查询值

例如：

Table [dbo].[Log_XML] contains columns Parametrs (xml),TimeEdit (datetime)

例如，参数中的XML：

<ns0:Record xmlns:ns0="http://Integration"> 
<MATERIAL>10</MATERIAL> 
<BATCH>A1</BATCH> 
</ns0:Record>

例如：查询：

select
 Parametrs,TimeEdit
from
 [dbo].[Log_XML]
where
 Parametrs.value('(//*:Record/BATCH)[1]', 'varchar(max)') like '%A1%'
 ORDER BY TimeEdit DESC

- romangorbenko.com

Answer 7

我使用下面的语句从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'

- ranjit

---

如果XML文件中没有命名空间定义怎么办？ - Muflix

Answer 8

如果你想查找除了"Alpha"之外的其他节点，查询应该像这样：

select Roles from MyTable where Roles.exist('(/*:root/*:role[contains(.,"Beta")])') = 1

- user2365332

Answer 9

您可以查询整个标签，或仅查询特定值。这里我使用通配符来匹配XML命名空间。

declare @myDoc xml
set @myDoc = 
'<Root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://stackoverflow.com">
    <Child>my value</Child>
 </Root>'

select @myDoc.query('/*:Root/*:Child') -- whole tag
select @myDoc.value('(/*:Root/*:Child)[1]', 'varchar(255)') -- only value

- FrankyHollywood