How can I check if and xml Field is not null or blank in sql sever

Question

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

create table #T (

ID int identity primary key,

XMLCol XML);

insert into #T (XMLCol)

select ' ';

insert into #T (XMLCol)

select '';

insert into #T

default values;

select *

from #T

where XMLCol is not null and cast(XMLCol as varchar(max)) != '';

That seems to work. Try it on yours.

Of course, using a cast function like that in the Where clause will slow the query down, but at least it will get the job done.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I think you may have posted in the wrong forum as this is a 2000 forum, but you should try this:

DECLARE @var XML

SET @var = '' -- '<test>test</test>'

SELECT @var.exist('/test')

IF @var IS NOT NULL AND @var.exist('/test') = 1

BEGIN

SELECT 'not null'

END

ELSE

BEGIN

SELECT 'null'

END

LeeLuv Mr or Mrs. 500 Points: 588 More actions · Answer 3

Mr or Mrs. 500

Points: 588

April 7, 2009 at 11:16 pm

Thanx this is working well ur a star

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Glad I could help.

Cookies on SQLServerCentral