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?
.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:
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:
.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
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:
- The xml methods act as a bridge between XQuery and SQL for the xml data types.
- 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.
