Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I check if and xml Field is not null or blank in sql sever Expand / Collapse
Author
Message
Posted Tuesday, April 07, 2009 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 11:55 PM
Points: 28, Visits: 173
I need to insert new records but frist I need to check if @PersonInfoXML is not null or @PersonInfoXML <> ''
It gives me this error ( The data types xml and varchar are incompatible in the equal to operator)
How can I do I check for is not null and not blank without getting this error
PLZ!!!!!!!!!!!!!
Help
Post #692148
Posted Tuesday, April 07, 2009 9:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #692200
Posted Tuesday, April 07, 2009 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
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





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #692212
Posted Tuesday, April 07, 2009 11:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 11:55 PM
Points: 28, Visits: 173
Thanx this is working well ur a star
Post #692763
Posted Wednesday, April 08, 2009 1:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
Glad I could help.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #692822
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse