See if XML element exists at any level in document with a specific value Is it possible to query XML to find if a particular element has a certain value? For example, if I wanted to see if the below XML has the value "Brandt" in &lt;ContactFName&gt;.

But note, the location of the element may change. In some cases it may be in /root/MCTLocations/MCTLocation, or it may jump to under the root, or appear somewhere else...

And, is it possible to parameterize the element name?

DECLARE @table TABLE (XmlCol XML)

INSERT INTO @table (XmlCol) VALUES ('
&lt;root&gt;
&lt;MCTClientName&gt;John&lt;/MCTClientName&gt;
&lt;MCTClientCity&gt;Palm Beach&lt;/MCTClientCity&gt;
&lt;MCTLocations&gt;
    &lt;MCTLocation&gt;
        &lt;Address&gt;1234 Main Street&lt;/Address&gt;
        &lt;ContactFName&gt;Chris&lt;/ContactFName&gt;
        &lt;ContactLName&gt;Brandt&lt;/ContactLName&gt;
    &lt;/MCTLocation&gt;
&lt;/MCTLocations&gt;
&lt;/root&gt;')

SELECT * FROM @table WHERE ??
For this you want to use the .exist() XML function as it will return a BIT (i.e. boolean) value indicating whether or not the XQuery find anything.

To handle the non-static location of an element, you would use either * (indicating that it should check all nodes of a particular level, but not other levels), or // (indicating that it should check all nodes at that level and below).

The following examples use the example query from the question as a base, and adds a few test cases to place the element at different levels, and adds a test case that changes the name to show that the XQuery isn't just selecting everything.

Test Setup (run once)

SET NOCOUNT ON;
CREATE TABLE #Table (ID INT NOT NULL, XmlCol XML);

INSERT INTO #Table (ID, XmlCol) VALUES (1, N'
&lt;root&gt;
&lt;MCTClientName&gt;John&lt;/MCTClientName&gt;
&lt;MCTClientCity&gt;Palm Beach&lt;/MCTClientCity&gt;
&lt;MCTLocations&gt;
    &lt;MCTLocation&gt;
        &lt;Address&gt;1234 Main Street&lt;/Address&gt;
        &lt;ContactFName&gt;Chris&lt;/ContactFName&gt;
        &lt;ContactLName&gt;Brandt&lt;/ContactLName&gt;
    &lt;/MCTLocation&gt;
&lt;/MCTLocations&gt;
&lt;/root&gt;');

INSERT INTO #Table (ID, XmlCol) VALUES (2, N'
&lt;root&gt;
&lt;MCTClientName&gt;John&lt;/MCTClientName&gt;
&lt;MCTClientCity&gt;Palm Beach&lt;/MCTClientCity&gt;
&lt;MCTLocations&gt;
    &lt;MCTLocation&gt;
        &lt;Address&gt;1234 Main Street&lt;/Address&gt;
        &lt;ContactFName&gt;Chris&lt;/ContactFName&gt;
        &lt;ContactLName&gt;Grandt&lt;/ContactLName&gt;
    &lt;/MCTLocation&gt;
&lt;/MCTLocations&gt;
&lt;/root&gt;');

INSERT INTO #Table (ID, XmlCol) VALUES (3, N'
&lt;root&gt;
&lt;MCTClientName&gt;John&lt;/MCTClientName&gt;
&lt;MCTClientCity&gt;Palm Beach&lt;/MCTClientCity&gt;
&lt;MCTLocations&gt;
    &lt;MCTLocation&gt;
        &lt;Address&gt;1234 Main Street&lt;/Address&gt;
        &lt;ContactFName&gt;Chris&lt;/ContactFName&gt;
    &lt;/MCTLocation&gt;
&lt;/MCTLocations&gt;
&lt;ContactLName&gt;Brandt&lt;/ContactLName&gt;
&lt;/root&gt;');

INSERT INTO #Table (ID, XmlCol) VALUES (4, N'
&lt;root&gt;
&lt;MCTClientName&gt;John&lt;/MCTClientName&gt;
&lt;MCTClientCity&gt;Palm Beach&lt;/MCTClientCity&gt;
&lt;MCTLocations&gt;
    &lt;MCTLocation&gt;
        &lt;Address&gt;1234 Main Street&lt;/Address&gt;
        &lt;ContactFName&gt;Chris&lt;/ContactFName&gt;
    &lt;/MCTLocation&gt;
    &lt;NewElement&gt;
       &lt;SubElement&gt;
          &lt;ContactLName&gt;Brandt&lt;/ContactLName&gt;
       &lt;/SubElement&gt;
    &lt;/NewElement&gt;
&lt;/MCTLocations&gt;
&lt;/root&gt;');

INSERT INTO #Table (ID, XmlCol) VALUES (5, N'
&lt;root&gt;
&lt;MCTClientName&gt;John&lt;/MCTClientName&gt;
&lt;MCTClientCity&gt;Palm Beach&lt;/MCTClientCity&gt;
&lt;MCTLocations&gt;
    &lt;MCTLocation&gt;
        &lt;Address&gt;1234 Main Street&lt;/Address&gt;
        &lt;ContactFName&gt;Chris&lt;/ContactFName&gt;
    &lt;/MCTLocation&gt;
    &lt;NewerElement&gt;
       &lt;ContactLName&gt;Brandt&lt;/ContactLName&gt;
    &lt;/NewerElement&gt;
&lt;/MCTLocations&gt;
&lt;/root&gt;');

INSERT INTO #Table (ID, XmlCol) VALUES (6, N'
&lt;root&gt;
&lt;MCTClientName&gt;John&lt;/MCTClientName&gt;
&lt;MCTClientCity&gt;Palm Beach&lt;/MCTClientCity&gt;
&lt;MCTLocations&gt;
    &lt;MCTLocation&gt;
        &lt;Address&gt;1234 Main Street&lt;/Address&gt;
        &lt;ContactFName&gt;Chris&lt;/ContactFName&gt;
    &lt;/MCTLocation&gt;
    &lt;NewerElement&gt;
    &lt;/NewerElement&gt;
&lt;/MCTLocations&gt;
&lt;/root&gt;
&lt;ContactLName&gt;Brandt&lt;/ContactLName&gt;
');


Test 1 (* in place of a node name)

This will check all nodes at the specified level, which in this case is just under &lt;root&gt;. But it won't check other levels.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/*/ContactLName[text()="Brandt"]') = 1;


Returns row with ID value of 3.

Test 2 (* in place of a node name)

This will check all nodes at the specified level, which in this case is just under &lt;root&gt;&lt;MCTLocations&gt;. But it won't check other levels.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations/*/ContactLName[text()="Brandt"]') = 1;


Returns rows with ID values of 1 and 5.

Test 3 (// in place of a node name)

This will check all nodes starting at the specified level, which in this case is just under &lt;root&gt;&lt;MCTLocations&gt;, and below.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations//ContactLName[text()="Brandt"]') = 1;


Returns rows with ID values of 1, 4, and 5.

Test 4 (/* or */ in place of a node name)

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'*//ContactLName[text()="Brandt"]') = 1;

-- and:

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//*/ContactLName[text()="Brandt"]') = 1;


Both return rows with ID values of 1, 3, 4, and 5.

These do not return row ID of 6 due to the * being a placeholder for a single node, hence the highest level allowed would be under &lt;root&gt; (or any top-level node).

Test 5 (// at the top level)

This will check all nodes starting at the top level.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()="Brandt"]') = 1;


Returns rows with ID values of 1, 3, 4, 5, and 6.

Test 6 (use local variable value for element text in XQuery)

DECLARE @Name NVARCHAR(50) = N'Brandt';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;

SET @Name = N'Grandt';

-- exact same query, just different value in the variable
SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;


First query returns rows with ID values of 1, 3, 4, 5, and 6.

Second query returns row with ID value of 2.

Test 7 (use function and string literal for element name in XQuery)

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()="NewerElement"]') = 1;


Returns rows with ID values of 5 and 6.

Test 8 (use function with local variable value for element name in XQuery)

DECLARE @Node NVARCHAR(50) = N'SubElement';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@Node")]') = 1;


Returns row with ID value of 4.

Test 9 (put all of the pieces together)

DECLARE @NodeName NVARCHAR(50) = N'ContactLName',
        @NodeText NVARCHAR(500) = N'Brandt';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@NodeName")]
   [text()=sql:variable("@NodeText")]') = 1;


Returns rows with ID values of 1, 3, 4, 5, and 6.



General XML Note:  

XML data (in SQL Server) is encoded as UTF-16 Little Endian, same as NVARCHAR / NCHAR. Hence it is best to prefix sting literals with a capital-N when the value is really XML.