SQL 2012 XML exist() Function NULL Comparison

The XML data type exist() function comparison of NULL XML instance to zero returns 0 and not 1 as in previous versions. Consider the following code:

DECLARE @test AS XML;

SET @test = NULL;

-- Returns 0 in SQL 2012, and 1 in earlier editions

SELECT COUNT(1) WHERE @test.exist('/whatever') = 0;

-- Returns 1 in all editions

SELECT COUNT(1) WHERE @test.exist('/whatever') IS NULL;

The first query returns 1 in previous editions, while according to other NULL comparisons in SQL Server 0 is expected. This behavior is more consistent than in previous versions.

Avtor: Anonymous, objavljeno na portalu SloDug.si (Arhiv)

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.