SQL Server XQuery: .exist() method

I’m trying to beef up my writing about XQuery in advance of the upcoming PASS Summit 2013; I got accepted to present, and I want to make sure that I start covering a lot of topics related to XML so that I’ve honed my focus a bit.  Unfortunately, blogging doesn’t come easy to me these days, and while I would generally start a session from the beginning and move forward, I think it would be best for me to start with something easy for me to write about, even though it means I’ll be doing a bit of backtracking later.

A little bit of background

As you may already know, SQL Server supports XQuery against an XML data type through the use of five basic methods:

  • .query()
  • .value()
  • .exist()
  • .modify()
  • .nodes()

Conceptually, each of these methods act a bridge between the the SQL processing engine and the xml parser baked into SQL Server.  This concept of a bridge allows me to think about where query work is being performed; is it being done as a SQL statement against the table containing the XML fragment of interest, or is it being performed by XQuery before bubbling up to the SQL level? 

image

.exist()

I want to focus on probably the easiest method against the xml datatype: .exist().  Simply put, this method checks to see whether or not the results of an XQuery expression against the supplied XML value are nonempty; it returns 1 if true, and 0 if false.  Let’s start with a simple sample:

DECLARE @T TABLE ( ID INT, x XML )

INSERT  INTO @T
        ( ID, x )
VALUES  ( 1, '<root><plant><fruit>tomato</fruit></plant></root>' )
,       ( 2, '<root><plant><fruit>banana</fruit></plant></root>' )
,       ( 3, '<root><plant><vegetable>tomato</vegetable></plant></root>' )


SELECT ID, X, x.exist('//root') AS "Does It Exist?" 
FROM @T 

Running this query will return the following results:

image

Every row has an XML value in the X column which contains a node named <root>.  We can change the output to identify rows which have vegetable nodes like so:

SELECT ID, X, x.exist('//root/plant/vegetable') AS "Does It Exist?" 
FROM @T 

The results then change to the following:

image

.exist() can also be used in the WHERE clause of a SQL statement; if we want to identify only the rows which have an XML fragment containing a tomato, we can alter the SQL statement like so:

SELECT ID, X  
FROM @T 
WHERE  x.exist('//.[text()="tomato"]') = 1

image

Note that the .exist() method interpreted an XQuery statement to look for nodes which had a text() value of “tomato”; two rows matched that requirement, so .exist() returned a 1 for those two rows to the calling SQL statement, and the WHERE clause identified those two rows.

Wrap-up

So, I’ve covered a few basic concepts while talking about the .exist() method:

  1. The xml methods act as a bridge between XQuery and SQL for the xml data types.
  2. The .exist() method supports XQuery expressions including both simple paths (//<node name>) and XQuery functions (e.g, text()).  This will become important later when I get around to discussing FLWOR.

June 19, 2013 · stuart · No Comments
Tags: , ,  · Posted in: SQL Server, SQLServerPedia Syndication, XML

Leave a Reply