Reputation: 192
I have a database field containing XML nested like this:
<configuration>
<modules>
<genericMailer>
<moduleContent>
<data>
<sendMethod>Email</sendMethod>
My XML can contain many instances of the <genericMailer>
element (and it's subnodes). I want to find all rows where there are any instances of <genericMailer>
where the <sendMethod>
contains 'Mail'.
I can get all of the values for sendMethod
like this:
SELECT a.ApplicationId,
x.XmlCol.value('(moduleContent/data/sendMethod)[1]','VARCHAR(100)') AS SendMethod
FROM Applications a
CROSS APPLY a.AppConfig.nodes('/configuration/modules/genericMailer') x(XmlCol);
however I don't know how to search only for matching values. What's the WHERE clause I need?
Upvotes: 1
Views: 1191
Reputation: 89335
Here are some possible ways :
a. Using value()
method to extract sendMethod
value then check if the value LIKE '%mail%'
in WHERE clause :
SELECT a.ApplicationId,
x.XmlCol.value('(moduleContent/data/sendMethod)[1]','VARCHAR(100)') AS SendMethod
FROM Applications a
CROSS APPLY a.AppConfig.nodes('/configuration/modules/genericMailer') x(XmlCol)
WHERE x.XmlCol.value('(moduleContent/data/sendMethod)[1]','VARCHAR(100)') LIKE '%mail%'
b. Using XPath method contains()
to check if sendMethod
value contains substring "mail"
and SQL Server method exist()
to filter rows that pass the check :
SELECT a.ApplicationId,
x.XmlCol.value('(moduleContent/data/sendMethod)[1]','VARCHAR(100)') AS SendMethod
FROM Applications a
CROSS APPLY a.AppConfig.nodes('/configuration/modules/genericMailer') x(XmlCol)
WHERE x.XmlCol.exist('moduleContent/data/sendMethod[contains(.,"mail")]') = 1
Upvotes: 2