Talk to the Experts

In the previous post, I discussed the value() method as part of my XQuery for the Non-Expert series.  This next post will deal with a topic similar to the exist() method – the determination of whether a element or attribute exists in an XML document.

We all need to filter data from time to time. We may be tempted to use the value() method to filter through XML documents.  The trouble is doing that can have a series impact on performance.  To prevent this issue, we need to use the exist() method.

Exist Syntax

The general syntax for the exist() method is:

exist (XQuery)

This method requires an XQuery statement as a parameter,  This parameter directs the exist() method to the element or attribute whose existence needs to be checked.  For a more detailed explanation of the XQuery statement, read my post on the nodes() method.

The exist() method returns a bit value that can represent one of three states:

• 1 – The exist method returned a True state.  This means that there is at least one XML node that matches the element or attribute from the XQuery parameter.
• 0 – The exist method returned a False state.  There are no elements or attributes in the XML document that match the XQuery parameter.
• NULL  – The exist method returned an empty state.  This will happen when the element or attribute from the XQuery contains a NULL value.

Exist Setup

As with the previous posts, we’ll use a setup script to play around with some variations in which the exist() method can be used:

IF OBJECT_ID('tempdb..#ExistExample') IS NOT NULL
DROP TABLE #ExistExample
GO
CREATE TABLE #ExistExample
(
XMLDocument xml
)
INSERT INTO #ExistExample
VALUES ('<Building type="skyscraper">
<Name>Sears Tower</Name>
<Floor level="1" />
<Floor level="2" />
<Floor level="3">
<Room number="3.1" internet="cable"/>
<Room number="3.2" internet="wifi"/>
</Floor>
<Floor level="4">
<Room number="4.1" bed="king" />
<Room number="4.2" bed="queen"/>
</Floor>
</Building>')
INSERT INTO #ExistExample
VALUES ('<Building type="skyscraper">
<Name>IDS Building</Name>
<Floor level="01" />
<Floor level="02" />
<Floor level="03" />
<Floor level="04">
<UtilityCloset />
<Room number="04.1" />
<Room number="04.2" />
</Floor>
</Building>')

Let’s put together some scenarios that will show some ways in which the exist() method can be leveraged.  These should cover most of the use cases for a non-expert with XQuery.

Element Exist Example

The first scenario that will come up is whether an element exists in an XML document.  For this, we’ll do two checks for the UtilityCloset element.

In the first check (FromRoot), the query will check to if the element exists anywhere in the XML document.  To do this the element name is prefixed with two forward slashes (“//”).  The query will also be filtered by this check to remove any XML documents that do not have a UtilityCloset element.

On the other check (FromNode), the exist() method will check to see if the UtilityCloset element is at the first level of the XML document.

Here’s the script for this query:

--UtilityCloset in XML document
SELECT c.query('.') AS XMLDocument
,c.query('//UtilityCloset') AS XMLFragment
,c.value('(Name/text())[1]','varchar(25)') AS BuildingName
,c.exist('//UtilityCloset') AS FromRoot
,c.exist('UtilityCloset') AS FromNode
FROM #ExistExample
CROSS APPLY XMLDocument.nodes('/Building') as t(c)
WHERE c.exist('//UtilityCloset') = 1

By referencing the XML documents above, only one XML document has UtilityCloset element.  Thus, only a single row was returned.  In the XMLFragemnt column, the query confirms that there is a UtilityCloset element and returns it.

Attribute Exist Example

We can also use this same concept with attributes.  In this example, instead of an element we’ll look for XML documents that have the internet attribute.

This query will also do searches for the attribute from the root of the XML document and at the node level.  The exist() method for the FromRoot will use double forward slashes (“//”).  Then the exist() method for the FromNode will use dot-single forward slash (“./”).  Of course, don’t forget to prefix the attribute name with an @ symbol.

--Internet in XML document
SELECT c.query('.') AS XMLFragment
,c.value('(//Building/Name/text())[1]','varchar(25)') AS BuildingName
,c.exist('//@internet') AS FromRoot
,c.exist('./@internet') AS FromNode
FROM #ExistExample
CROSS APPLY XMLDocument.nodes('/Building/Floor/Room') as t(c)
WHERE c.exist('//@internet') = 1

And these results will look like those below.  Notice that the match was able to find an attribute a few levels deep.

Element Value Equals Example

Next, we might want to find all of the elements with specific text() values.  In the above XML document, there is a Name element.  To check this existence we’ll look for the Sears Tower value.

This can be accomplished by appending brackets with an equality statement to the XQuery path parameter of the exists() method.  The query below demonstrates implementing this:

--Element with the value "Sears Tower"
SELECT c.query('.') AS XMLFragment
,c.value('(Name/text())[1]','varchar(25)') AS BuildingName
FROM #ExistExample
CROSS APPLY XMLDocument.nodes('/Building') as t(c)
WHERE c.exist('/Building/Name[.="Sears Tower"]') = 1

The results of the query are below.  Instead of two rows, only the XML document for the Sears Tower was returned.

Attribute Value Equals Example

Of course, what can be done with the element can also be done with the attribute.  In the next example, the query will find the XML fragment with bed attribute equal to “queen”.

Again, in the FromRoot column the entire XML document is being checked.  Then in the FromNode the node from the XML fragment.  The syntax for attribute values is just like using the search for attributes themselves.  This query looks like the following:

--Rooms with beds
SELECT c.query('.') AS XMLFragment
,c.value('(@number)','decimal(3,1)') AS RoomNumber
,c.value('(@bed)','varchar(10)') AS BedType
,c.exist('/Building/Floor/Room[@bed = "queen"]') AS FromRoot
,c.exist('.[@bed = "queen"]') AS FromNode
FROM #ExistExample
CROSS APPLY XMLDocument.nodes('/Building/Floor/Room') as t(c)

The results of this query are as they are below:

As you can see the XQuery is able to indicate whether the bed attribute has a value at both the root and the node levels.

Exist Wrap-Up

These examples should help explain how to leverage the exist() method.  And if things go well, you won’t have the same issues and confusion that I experienced trying to figure this out.  The exist() method is a powerful method in XQuery and will be crucial with writing well performing queries.

As with the other posts, if anything in this post needs further clarity please comment below.

Related posts:

1. XQuery for the Non-Expert – Value
2. XQuery for the Non-Expert – Query
3. XQuery for the Non-Expert – Terminology