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

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

  • LeeLuv

    Mr or Mrs. 500

    Points: 588

    Thanx this is working well ur a star 😎

  • Jack Corbett

    SSC Guru

    Points: 184361

    Glad I could help.

    Jack Corbett
    Consultant - Straight Path Solutions
    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

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

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