blocks|key|585640|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|585641|如果希望查询以where+col+like+'%25Beta%25'方式工作，则可以使用contains|unstyled|offset|length|style|CODE|585642|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|585643|entityMap^0|0|7|N|14|8|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]|$G|S|H|T|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|M|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|N|$]]

<pre class="lang-sql prettyprint-override"><code>declare @T table(Roles xml)

insert into @T values
('&lt;root&gt;
 &lt;role&gt;Alpha&lt;/role&gt;
 &lt;role&gt;Beta&lt;/role&gt;
 &lt;role&gt;Gamma&lt;/role&gt;
&lt;/root&gt;')

declare @Role varchar(10)

set @Role = 'Beta'

select Roles
from @T
where Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1
</code></pre>

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

<pre class="lang-sql prettyprint-override"><code>declare @T table(Roles xml)

insert into @T values
('&lt;root&gt;
 &lt;role&gt;Alpha&lt;/role&gt;
 &lt;role&gt;Beta&lt;/role&gt;
 &lt;role&gt;Gamma&lt;/role&gt;
&lt;/root&gt;')

declare @Role varchar(10)

set @Role = 'et'

select Roles
from @T
where Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1
</code></pre>

blocks|key|369886|text|如果您的字段名为Roles，表名为table1，则可以使用以下命令进行搜索|type|unstyled|depth|inlineStyleRanges|entityRanges|data|369887|DECLARE+@Role+varchar(50);
SELECT+*+FROM+table1
WHERE+Roles.exist+('/root/role+=+sql:variable("@Role")')+=+1|code-block|syntax|javascript|369888|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

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

blocks|key|585842|text|我想出了一个简单的工作，下面也很容易记住:-)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|585843|select+*+from++
(select+cast+(xmlCol+as+varchar(max))+texty
+from+myTable+(NOLOCK)+
)+a+
where+texty+like+'%25MySearchText%25'|code-block|syntax|javascript|585844|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>select * from 
(select cast (xmlCol as varchar(max)) texty
 from myTable (NOLOCK) 
) a 
where texty like '%MySearchText%'
</code></pre>

blocks|key|585770|text|您可以执行以下操作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|585771|declare+@role+varchar(100)+=+'Alpha'
select+*+from+xmltable+where+convert(varchar(max),xmlfield)+like+'%25<role>'%2B@role%2B'</role>%25'|code-block|syntax|javascript|585772|显然，这是一个小技巧，我不建议任何正式的解决方案使用它。但是，在SQL+Server+Management+Studio+for+SQL+Server2012中对XML列执行即席查询时，我发现此技术非常有用。|585773|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You could do the following

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

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.

blocks|key|370272|text|有用的提示。查询SQL+Server+XML列中的值(具有命名空间的XML)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|370273|例如：|370274|Table+[dbo].[Log_XML]+contains+columns+Parametrs+(xml),TimeEdit+(datetime)|code-block|syntax|javascript|370275|例如，参数中的XML：|370276|<ns0:Record+xmlns:ns0="http://Integration">+
<MATERIAL>10</MATERIAL>+
<BATCH>A1</BATCH>+
</ns0:Record>|370277|例如查询：|370278|select
+Parametrs,TimeEdit
from
+[dbo].[Log_XML]
where
+Parametrs.value('(//*:Record/BATCH)[1]',+'varchar(max)')+like+'%25A1%25'
+ORDER+BY+TimeEdit+DESC|370279|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Useful tip. Query a value in SQL Server XML column (XML with namespace)

e.g. 

<pre><code>Table [dbo].[Log_XML] contains columns Parametrs (xml),TimeEdit (datetime)
</code></pre>

e.g. XML in Parametrs:

<pre><code>&lt;ns0:Record xmlns:ns0="http://Integration"&gt; 
&lt;MATERIAL&gt;10&lt;/MATERIAL&gt; 
&lt;BATCH&gt;A1&lt;/BATCH&gt; 
&lt;/ns0:Record&gt;
</code></pre>

e.g. Query:

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

blocks|key|370047|text|我使用下面的语句在Sql表中检索XML中的值|type|unstyled|depth|inlineStyleRanges|entityRanges|data|370048|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+'%25EMAIL%25'+and+olc.Value+like+'%25EMAIL%25'+and+olc.Locale='UK+EN'|code-block|syntax|javascript|370049|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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'
</code></pre>

blocks|key|370302|text|您可以查询整个标记，也可以只查询特定的值。在这里，我对xml名称空间使用通配符。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|370303|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|code-block|syntax|javascript|370304|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can query the whole tag, or just the specific value. Here I use a wildcard for the xml namespaces.
<pre><code>declare @myDoc xml
set @myDoc = 
'&lt;Root xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot; xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot; xmlns=&quot;http://stackoverflow.com&quot;&gt;
 &lt;Child&gt;my value&lt;/Child&gt;
 &lt;/Root&gt;'

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

blocks|key|370330|text|如果您想要查找"Alpha“之外的其他节点，查询应该是这样的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|370331|select+Roles+from+MyTable+where+Roles.exist('(/*:root/*:role[contains(.,"Beta")])')+=+1|offset|length|style|CODE|370332|entityMap^0|0|0|2F|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

In case you want to find other node besides &quot;Alpha&quot;, the query should be something like this:
<code>select Roles from MyTable where Roles.exist('(/*:root/*:role[contains(.,&quot;Beta&quot;)])') = 1</code>

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

<pre><code>&lt;root&gt;
 &lt;role&gt;Alpha&lt;/role&gt;
 &lt;role&gt;Beta&lt;/role&gt;
 &lt;role&gt;Gamma&lt;/role&gt;
&lt;/root&gt;
</code></pre>

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

How can I query a value in SQL Server XML column

我将以下XML存储在SQL Server数据库的XML列(名为Roles)中。<root> <role>Alpha</role> <role>Beta</role> <role>Gamma</role></root>我想列出在其中具有特定角色的所有行。此角色通过参数传递。

问如何查询SQL Server XML列中的值
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何查询SQL Server XML列中的值EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何查询SQL Server XML列中的值
EN