匹配嵌套 XML 节点内容的 SQL

我有一个包含 XML 嵌套的数据库字段，如下所示:

<预><代码><配置><模块><genericMailer><模块内容><数据><sendMethod>电子邮件</sendMethod>

我的 XML 可以包含 元素(及其子节点)的许多实例.我想找到所有 实例的所有行，其中 包含Mail".

我可以像这样获取 sendMethod 的所有值:

SELECT a.ApplicationId,x.XmlCol.value('(moduleContent/data/sendMethod)[1]','VARCHAR(100)') AS SendMethod来自应用程序 a交叉应用 a.AppConfig.nodes('/configuration/modules/genericMailer') x(XmlCol);

但是我不知道如何只搜索匹配的值.我需要什么 WHERE 子句?

解决方案

这里有一些可能的方法:

一个.使用 value() 方法提取 sendMethod 值，然后检查 WHERE 子句中的值是否为 LIKE '%mail%' :

SELECT a.ApplicationId,x.XmlCol.value('(moduleContent/data/sendMethod)[1]','VARCHAR(100)') AS SendMethod来自应用程序 a交叉应用 a.AppConfig.nodes('/configuration/modules/genericMailer') x(XmlCol)WHERE x.XmlCol.value('(moduleContent/data/sendMethod)[1]','VARCHAR(100)') LIKE '%mail%'

B.使用 XPath 方法 contains() 检查 sendMethod 值是否包含子字符串 "mail" 和 SQL Server 方法 exist() 过滤通过检查的行:

SELECT a.ApplicationId,x.XmlCol.value('(moduleContent/data/sendMethod)[1]','VARCHAR(100)') AS SendMethod来自应用程序 a交叉应用 a.AppConfig.nodes('/configuration/modules/genericMailer') x(XmlCol)WHERE x.XmlCol.exist('moduleContent/data/sendMethod[contains(.,"mail")]') = 1

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?

解决方案

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