問題描述

我有一個包含 XML 嵌套的數據庫字段，如下所示:

我的 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

這篇關于匹配嵌套 XML 節點內容的 SQL的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！