Post reply

COALESCE with XML data

  • October 9, 2014 at 2:02 pm

    #300042

    looking to coalesce a WHERE clause with XML data.

    I cannot supply test data. Can someone look at the COALESCE syntax and let me know what I'm doing wrong?

    If 472 is null, check the NEXT XML field at grab the 150, if both 472 AND 150 do NOT exist let it be NULL.

    set @Date = (select top 1 XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)') from #src

    where coalesce(XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472', '(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150')

  • October 11, 2014 at 12:31 am

    #1751780

    SQLSeTTeR (10/9/2014)

    looking to coalesce a WHERE clause with XML data.

    I cannot supply test data. Can someone look at the COALESCE syntax and let me know what I'm doing wrong?

    If 472 is null, check the NEXT XML field at grab the 150, if both 472 AND 150 do NOT exist let it be NULL.

    set @Date = (select top 1 XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)') from #src

    where coalesce(XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472', '(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150')

    Your SQL syntax is invalid and it's really hard to tell what you're attempting to accomplish from your post, but here is what I think you want. Post back with more detail if that's not it.

    SET @Date = COALESCE((

    SELECT TOP 1

    XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)')

    FROM #src

    WHERE XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472'

    ), (

    SELECT TOP 1

    XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)')

    FROM #src

    WHERE XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150'

    ));

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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