how to test if XML = '' in sql server?
You may check for NULL
and nodes absence (exist
method of xml
type):
@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('*') = 0
Your XPath can be more specific, if necessary:
@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('/BulletPoint/NoteTypeCode/text()') = 0
Another way to test for an empty XML parameter, variable, or column is to check the DATALENGTH
. Any empty XML item should be 5 bytes. For example:
DECLARE @Test TABLE (XmlParam XML NULL);
INSERT INTO @Test ([XmlParam]) VALUES (NULL), (N''), (N'g');
SELECT t.[XmlParam],
DATALENGTH(t.[XmlParam]) AS [DATALENGTH],
CASE (ISNULL(DATALENGTH(t.[XmlParam]), 5))
WHEN 5 THEN 'EMPTY'
ELSE 'Not Empty'
END AS [IsEmpty]
FROM @Test t;
Returns:
XmlParam DATALENGTH IsEmpty
-------- ---------- ---------
NULL NULL EMPTY
5 EMPTY
g 9 Not Empty
Please note that I used ISNULL(DATALENGTH(t.[XmlParam]), 5)
as that should be fine for checking a parameter or variable. If checking a column, it might be better to use XmlColumn IS NULL OR DATALENGTH(XmlColumn) = 5
.
Please also note that while it is possible for the internal representation of XML data to change between versions, I have tested on SQL Server 2008 R2, 2012, and 2014 and the size of an empty XML item is consistently 5.