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

  • Question

  • 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



    • Edited by Sudip_inn Wednesday, March 25, 2020 3:31 PM
    Wednesday, March 25, 2020 3:11 PM

Answers

  • Try this:

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

     

    or this:

    @IsDeleted = ISNULL(NULLIF(col.value('IsDeleted[1]','CHAR(1)'), ''), 'N')

    • Edited by Viorel_MVP Wednesday, March 25, 2020 4:07 PM
    • Marked as answer by Sudip_inn Wednesday, March 25, 2020 7:02 PM
    Wednesday, March 25, 2020 3:59 PM

All replies

  • Try this:

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

     

    or this:

    @IsDeleted = ISNULL(NULLIF(col.value('IsDeleted[1]','CHAR(1)'), ''), 'N')

    • Edited by Viorel_MVP Wednesday, March 25, 2020 4:07 PM
    • Marked as answer by Sudip_inn Wednesday, March 25, 2020 7:02 PM
    Wednesday, March 25, 2020 3:59 PM
  • thanks for the reply. i will try but i like to know why you use both ISNULL & NULLIF ?

    @IsDeleted = ISNULL(NULLIF(col.value('IsDeleted[1]','CHAR(1)'), ''), 'N')

    Wednesday, March 25, 2020 6:09 PM
  • thanks a lot. it worked.

    working code

    declare @xml xml=N'<?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>'
    
    DECLARE @PeriodType VARCHAR(20), @Period VARCHAR(30), @IsDeleted CHAR(1)
    		DECLARE @MasterID INT
    		DECLARE @i INT, @cnt INT = @xml.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 = ISNULL(col.value('(IsDeleted/text())[1]','VARCHAR(30)'), 'N')
    			  FROM @xml.nodes('/Root/PeriodData[position() = sql:variable("@i")]') AS tab(col);
    			
    			  PRINT @PeriodType + ' '+@Period+' '+@IsDeleted
    
    		   SET @i += 1;
    		END

    • Edited by Sudip_inn Wednesday, March 25, 2020 7:03 PM
    Wednesday, March 25, 2020 7:02 PM