How to check child node exist or not in xml by xquery

[Sudip_inn]

see this how i am iterate in data which i am extracting from xml.

my xml

<?xml version="1.0" encoding="utf-16"?>
<Root>
<PeriodData>
<PeriodType>ANNUALONLY</PeriodType>
<Period>2010 FY</Period>
</PeriodData>
<PeriodData>
<PeriodType>ANNUALONLY</PeriodType>
<Period>2011 FY</Period>
<IsDeleted/>
</PeriodData>
<PeriodData>
<PeriodType>ANNUALONLY</PeriodType>
<Period>2011 FY</Period>
<IsDeleted>Y</IsDeleted>
</PeriodData>
</Root>
my code in SP by which i am iterating in loop and extracting data from xml by xquery

DECLARE @PeriodType VARCHAR(20), @Period VARCHAR(30), @IsDeleted CHAR(1)
DECLARE @MasterID INT
DECLARE @i INT, @cnt INT = @Periods.value('count(/Root/PeriodData)', 'INT');

SET @i = 1;
WHILE @i <= @cnt BEGIN


SELECT @PeriodType = col.value('(PeriodType/text())[1]','VARCHAR(20)')
, @Period = col.value('(Period/text())[1]','VARCHAR(30)')
, @IsDeleted = col.value('(IsDeleted/text())[1]','VARCHAR(30)')
FROM @Periods.nodes('/Root/PeriodData[position() = sql:variable("@i")]') AS tab(col);

IF NOT EXISTS (SELECT * FROM tblCalenderDetail WHERE PeriodType=@PeriodType AND Period=@Period AND IsDeleted='Y')
BEGIN
INSERT INTO tblCalenderDetail (MasterID,PeriodType,Period,IsDeleted)
VALUES(@MasterID,@PeriodType,@Period,'N')
END
ELSE
BEGIN
UPDATE tblCalenderDetail SET IsDeleted='N' WHERE PeriodType=@PeriodType AND Period=@Period
END

SET @i += 1;
END

See IsDeleted child node does not exist in first record and IsDeleted child node does exist in 2nd row but has null value.

so tell me how could i check IsDeleted child node does exist or not when iterating in while loop ?

i tried this below code to check whether IsDeleted node exist in each row but did not work rather throwing error

@IsDeleted = IIF(col.exist('//IsDeleted') , col.value('(IsDeleted/text())[1]','VARCHAR(30)') ,'N')

in loop if want to check if IsDeleted Node exist then its value will be stored in @IsDeleted variable...if node does not exist then i will store 'N' in @IsDeleted variable. how to achieve this?

please give me solution with rectified code. thanks

Continue reading...