August 12, 2009

SQL SERVER: Check if Node exists in XML or not 









Today, I have one requirement to check dynamically if a node exists in my xml or NOT. 



I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic. 



I figure it out by using EXISTS.



This is my XML, that I got as parameter.




DECLARE @ExportData  XML


SELECT @ExportData =


'<Data Number="A123">


  <BulkData>


    <EachData Parts="Test1" />


    <EachData Parts="Test2" />


    <EachData Parts="Test3" />


  </BulkData>


</Data>'



Now I need to check if "BulkData" node exists in XML, then I need to write different logic to get the result. 

So, I used this




SELECT @ExportData.exist('(//BulkData)')



This will return "1" if node is exists else return "0".



That's it. I can write based on the return result by this statement.



Let me know if it helps you.



Reference : Tejas Shah(http://www.SQLYoga.com)














Posted by























Labels:
,
,
,
,
,
,



















15 comments:








  1. AnonymousOctober 23, 2009 at 8:05 PM
    Good one! Helped me
    ReplyDelete
  2. AnonymousMarch 26, 2010 at 1:07 AM
    Thanks as well.  Once comment.  Routinely I see people using "//" in xpaths.  Bear in mind that when this criteria is entered as the only criteria or as the beginning part of the criteria as in this example //BulkData the parser will search the entire document and all node levels for any existance of BulkData.  While a small document has likely little impact, larger documents can be very signifcant.  Better to explicitly set the path as "/Data/BulkData" which will perform the best.
    ReplyDelete
  3. AnonymousMarch 31, 2012 at 1:38 AM
    Very helfpful... thanks for sharing...
    ReplyDelete
  4. AnonymousJanuary 23, 2013 at 2:27 AM
    It worked!! Thanks a lot
    ReplyDelete
  5. AnonymousJuly 10, 2013 at 4:22 PM
    I have a very big xml file,which are coming from SQL table column,the xml files are very big and its saved in xml column in the table.I need to convert all the nodes into column and the values inside nodes as rows.I have done some of the developement where I can see all the Nodes and its values but in the rows.I only need to show all the node values in table column in selec t query
    ReplyDelete
  6. Tejas ShahJuly 10, 2013 at 4:31 PM
    Hi,

    Can you please provide some sample data with expected output? So will try to solve your problem.

    Thanks,
    Tejas
    SQLYoga
    ReplyDelete
  7. AnonymousJuly 10, 2013 at 5:01 PM
    Hi,Thanks a lot for Instatnt reply.
    I have a big xml as below:
    - 
    - 
      2.6 
       
      false 
      
    - 
    - 
      Personal 
       
      Test DueDateNew 
      7 
      2012-08-23 
      2012-08-24T12:20:58.093 
       
       
       
       
      Unchecked 
    - 
    - 
      Unchecked 
      2012-08-23 
      Here,I have created one query,whihc is taking data from all Nodes and Values,the query is as below:

    ;WITH XMLNAMESPACES('http://www.Google.co.k/f/1.0' as xsi)


    SELECT 
     T.C.value('local-name(.)','nvarchar(max)'),  
     T.C.value('(/.)', 'varchar(500)'),
     T.C.query    ('declare namespace MI="http://www.Google.co.u/f/1.0";
             ( /*/*)') ,
    T.C.value('(//*[local-name()!=" "])[1]', 'nvarchar(max)')


    FROM [DB].[Table] Cross apply
    xml.nodes('//*') AS T(C);


    But,the nodes name coming in the rows need to come in the columns and the values in rows.
    If also I can create any table,where all the columns could be created as per nodes and then we can insert the values and select.
    Please provide help.
    Regards,
    Nitesh
    ReplyDelete
  8. AnonymousJuly 10, 2013 at 5:01 PM
    Sorry,I was not able to send full xml due to size limit.But its really a big xml approx 3600 nodes.
    ReplyDelete
  9. Tejas ShahJuly 10, 2013 at 6:20 PM
    Hi Nitesh,

    Can you have some part of the XML, so can understand the structure of the XML along with expected output?

    Tejas
    SQLYoga
    ReplyDelete
  10. AnonymousJuly 10, 2013 at 6:43 PM

      
        2.6
        
        false
      
      
        
          Personal
          
          
          
          2012-06-27
          2012-09-06T13:00:00.000
          
          
          
          B U
          
            
              B U
              2012-06-27
            
          
          L
          
            
              L
              CreateCase
              rner
            
          
          
            Pr
          
          Point of Sale
          Partial
          
          
          
          
          
          
          
            
              Advised
            
          
        
        
        
          
            Client
            Individual
            Prospect
            
              
                Prospect
                CreateCase
                A G
              
            
            
              192
            
                   BC
            
            BC
            
            YZ
            Male
            10
            Married
            
              
                Residential Address
                Current
                
                  
                  1.222
                  
                  
                  AB
                  CD
                  RG
                  CN
                
                
                  
                  123456789
                  
                
              
            
          
        
        
        
        
        
          false
        
        
          false
        
        
        
          false
        
        
          
         
        
        
        
        
        
        
        
        
        
        
        
          
          
          
          
            
          
          
          
          
          false
        
        
         
          
            
              Import
            
            
              
            
          
        
        
      
    ReplyDelete
  11. Tejas ShahJuly 10, 2013 at 6:45 PM
    Hi Nitesh,

    Can you please send an email with attachment of XML file to tejas[at]sqlyoga.com?

    Thanks,
    Tejas
    ReplyDelete
  12. AnonymousJuly 10, 2013 at 6:48 PM
    I have provided full xml to you.Please suggest.
    ReplyDelete
  13. Tejas ShahJuly 10, 2013 at 7:08 PM
    Hi Nitesh,

    Can you please send an email with attachment of XML file to tejas[at]sqlyoga.com?

    Thanks,
    Tejas 
    ReplyDelete
  14. AnonymousJuly 18, 2013 at 7:40 PM
    Hi,any update on this please?
    ReplyDelete
  15. Tejas ShahJuly 19, 2013 at 11:16 AM
    Hi Nitesh,

    Can you please send an email with attachment of XML file to tejas[at]sqlyoga.com? Once I have XML file, I will try to solve.

    Thanks,
    Tejas
    SQLYoga
    ReplyDelete




























        

      









Subscribe to:
Post Comments (Atom)