HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

See if XML element exists at any level in document with a specific value

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlsql-serverxmlt-sqlstackoverflowdba
levelwithanyxmlseevalueelementdocumentexistsspecific

Problem

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

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 ('

John
Palm Beach

    
        1234 Main Street
        Chris
        Brandt
    

')

SELECT * FROM @table WHERE ??

Solution

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'

John
Palm Beach

    
        1234 Main Street
        Chris
        Brandt
    

');

INSERT INTO #Table (ID, XmlCol) VALUES (2, N'

John
Palm Beach

    
        1234 Main Street
        Chris
        Grandt
    

');

INSERT INTO #Table (ID, XmlCol) VALUES (3, N'

John
Palm Beach

    
        1234 Main Street
        Chris
    

Brandt
');

INSERT INTO #Table (ID, XmlCol) VALUES (4, N'

John
Palm Beach

    
        1234 Main Street
        Chris
    
    
       
          Brandt
       
    

');

INSERT INTO #Table (ID, XmlCol) VALUES (5, N'

John
Palm Beach

    
        1234 Main Street
        Chris
    
    
       Brandt
    

');

INSERT INTO #Table (ID, XmlCol) VALUES (6, N'

John
Palm Beach

    
        1234 Main Street
        Chris
    
    
    

Brandt
');


Test 1 (* in place of a node name)

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

Code Snippets

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>
');
SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/*/ContactLName[text()="Brandt"]') = 1;
SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations/*/ContactLName[text()="Brandt"]') = 1;
SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations//ContactLName[text()="Brandt"]') = 1;
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;

Context

StackExchange Database Administrators Q#152680, answer score: 19

Revisions (0)

No revisions yet.