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 7, 2009 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 23, 2014 5:28 AM
Points: 28, Visits: 174
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 7, 2009 9:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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 7, 2009 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 11,157, Visits: 12,899
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 7, 2009 11:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 23, 2014 5:28 AM
Points: 28, Visits: 174
Thanx this is working well ur a star
Post #692763
Posted Wednesday, April 8, 2009 1:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 11,157, Visits: 12,899
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