Check if SQL Server 2005 XML field is empty

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 xml field (not nullable) where ScopeValue column has empty entries means zero chars.

Anyone knows?

Bauble

Answer 1

Try this:

 DELETE FROM dbo.G_Scope WHERE ScopeValue IS NULL

The SQL Server column would be NULL is if contains no value.

The other possibility would be that the XML is not NULL, but contains an empty string as its value. For that, use this command:

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

Does that show you the rows you're interested in?

Wrongly

thats odd..., either I did this before Is NULL... and it seems I have a sql studio management display refresh problem. I can do selects on the table again and again its showing ScopeValues with empty fields... – Bauble

@Wrongly I believe the datalength of an empty XML var would be 5: declare @x xml; set @x=''; select datalength(@x); select cast(@x as varbinary(max)) – Grafton

Answer 2

This works for me:

DELETE FROM MyTable
WHERE ScopeValue.exist('/node()') = 1;

Insulate

Please, edit and try for How to Answer, describe the effect of what you propose and explain why it helps to solve the problem. Find help with formatting your post here: stackoverflow.com/help/formatting . Consider taking the tour. – Girdle