CODEWITHSUNDEEP
t-sqlsql-server-2012xquery
GoldBishop
GoldBishop

Reputation: 2861

Check if any field has empty value in a table

I recently, approx 2 months ago, needed to check for any field in a table that has NULL value.

I am now onto another task but this time i need to check if any field in a table has an Empty String value.

Starting Query:

;With xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
SELECT *
FROM [Schema].[Table] act
where (
    select act.*
    for xml path('row'), elements xsinil, type
).exist('//*/@ns:nil') = 1

I know i need to change @ns:nil but as i am uneducated on TSql's XQuery implementation, i need someone to help me with this initial query. As well, where i should go outside of MSDN to get read up on usage and functionality.

Update #1:

;With xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
Select *
from Schema.Table act
where (
    select act.*
    for xml path('row'), elements xsinil, type
).value('(//row/*)[1]', 'varchar(max)') = ''

Tried this but evidently one of the fields contains character 0x001C and so requires a conversion to binary, varbinary, or image and then use BINARY BASE64 directive.

Upvotes: 1

Views: 769

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Build the XML and check for node values that are empty. Simpler than checking for null and as stated in comment, only (n)varchar produces an empty string as a node value. 

select *
from T
where (
      select T.*
      for xml path(''), type
      ).exist('*/text()[. = ""]') = 1

Upvotes: 1

Related Questions