Feeds:
Posts
Comments

Archive for the ‘xml’ Category

I have been using exist() function for a long time. This is very handy when we need to check the existence of any data in the xml document. There is also a bug related to this function when it comes to NULL but this bug is fixed in SQL Server 2012.

Let me explain it with examples.
Example 1 :
Create the given below script in ealier version of SQL Server (2005 & 2008)

DECLARE @xmltest XML;
SET @xmltest = null;
SELECT COUNT(*) as [T_Count] WHERE @xmltest.exist('/raresql') = 0;
--OUTPUT

T_Count
———–
1

(1 row(s) affected)

Ooopps…… The expected result is 0 because ‘/raresql’ doesn’t exist in the @xmltest but it returned 1 .

Example 2 :
Lets create the same sample in SQL Server 2012 and view the output.

DECLARE @xmltest XML;
SET @xmltest = null;
SELECT COUNT(*) as [T_Count] WHERE @xmltest.exist('/raresql') = 0;
--OUTPUT

T_Count
———–
0

(1 row(s) affected)

The expected result is 0 because ‘/raresql’ doesn’t exist in the @xmltest and as expected the query returned 0 as well.

Conclusion :
The behavior issue with exist() function on xml datatype is fixed in SQL Server 2012. If the SQL Server is upgraded from earlier version to 2012 and if some scripting is added to correct this behavior in the earlier version, then these scripts should be removed.

Reference : MSDN

Read Full Post »