Check if SQL Server 2005 XML field is empty

sql-server xml-column is-empty
msfanboy picture msfanboy · Jun 25, 2010 · Viewed 10.2k times · Source

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?

Answer

marc_s picture marc_s · Jun 25, 2010

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?

Related questions

How do I update an XML column in sql server by checking for the value of two nodes including one which needs to do a contains (like) comparison

I have an xml column called OrderXML in an Orders table... there is an XML XPath like this in the table... /Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail There InternalOrderDetails contains many InternalOrderDetail nodes like this... <InternalOrderDetails> <InternalOrderDetail&…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More