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

  • LeeLuv

    Mr or Mrs. 500

    Points: 588

    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

  • GSquared

    SSC Guru

    Points: 260824

    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: 184296

    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 Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • LeeLuv

    Mr or Mrs. 500

    Points: 588

    Thanx this is working well ur a star 😎

  • Jack Corbett

    SSC Guru

    Points: 184296

    Glad I could help.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

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

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