Sql-server – See if XML element exists at any level in document with a specific value

sql servert-sqlxml

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 <ContactFName>.

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 ('
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
        <ContactLName>Brandt</ContactLName>
    </MCTLocation>
</MCTLocations>
</root>')

SELECT * FROM @table WHERE ??

Best Answer

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'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
        <ContactLName>Brandt</ContactLName>
    </MCTLocation>
</MCTLocations>
</root>');

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

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

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

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

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

Test 1 (* in place of a node name)

This will check all nodes at the specified level, which in this case is just under <root>. 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 <root><MCTLocations>. 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 <root><MCTLocations>, 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 <root> (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.

Related Question