CODEWITHSUNDEEP
sql-serverxml
Izzy
Izzy

Reputation: 192

SQL to match the content of a nested XML node

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

Answers (1)

har07
har07

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

Related Questions