Read Null and Empty string from xml in SQL Server

  • Hi,

    This Code (in bottom) give the result NULL for the 2 fields HID1 and HID2 but I want to use the same syntax on 1 and 2 to get Null for HID1 and empty string for HID2 without using "case when ..."

    In another words : HID1 (or HID2) can come with nil attribute or without it so I want one syntax (without using "case when ...") give me Null For the first and empty string for the second.

    The Code is :

    DECLARE @AllInfo xml

    SET @AllInfo = '<RooT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >

    <HID1 xsi:nil="true" />

    <HID2 />

    </RooT>'

    select

    node1.value('(HID1/text())[1]','varchar(64)') HID1, -- 1

    node1.value('(HID2/text())[1]','varchar(64)') HID2 -- 2

    FROM @AllInfo.nodes('/RooT') T(node1)

    Thank you .

  • I want one simple syntax without use "case when" to do the following work :

    DECLARE @AllInfo xml

    SET @AllInfo = '

    <RooT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >

    <HID xsi:nil="true" />

    </RooT>'

    select

    (

    case when node1.value('(HID/@xsi:nil)[1]','varchar(64)') = 'true'

    then NULL

    when (node1.value('(HID/@xsi:nil)[1]','varchar(64)') is null or node1.value('(HID/@xsi:nil)[1]','varchar(64)') = 'false') and node1.value('(HID/text())[1]','varchar(64)') is null

    then ''

    else

    node1.value('(HID/text())[1]','varchar(64)')

    end) HID

    FROM @AllInfo.nodes('/RooT') T(node1)

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

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