December 1, 2014 at 8:48 am
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 .
December 1, 2014 at 10:14 am
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