Read Null and Empty string from xml in SQL Server

• mazen khoder

  SSC Enthusiast

  Points: 184

  More actions

  December 1, 2014 at 8:48 am

  #312818

  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 .

• mazen khoder

  SSC Enthusiast

  Points: 184

  More actions

  December 1, 2014 at 10:14 am

  #1761791

  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)