Check for SQL Server 2005 XML Fields

Question

I just did this:

Delete FROM MyTable WHERE ScopeValue = "" Delete FROM G_Scope WHERE ScopeValue is '' Delete FROM G_Scope WHERE ScopeValue = empty Delete FROM G_Scope WHERE ScopeValue is empty 

I want to delete all rows with an xml field (cannot be nullable), where the ScopeValue column has empty entries, means null characters.

Somebody knows?

Answer 1

Try the following:

  DELETE FROM dbo.G_Scope WHERE ScopeValue IS NULL 

The SQL Server column will be NULL if it does not contain a value.

Another possibility is that XML is not NULL, but contains an empty string as its value. To do this, use the following command:

 -- The DATALENGTH of an empty XML column is 5 SELECT * FROM dbo.G_Scope WHERE DATALENGTH(ScopeValue) = 5 

Does this mean which lines you are interested in?