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





at
12:50 PM




















Labels:
,
,
,
,
,
,















3
comments:
      









  1. Good one! Helped me
    ReplyDelete
  2. 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. Very helfpful... thanks for sharing...
    ReplyDelete




































        

      










Subscribe to:
Post Comments (Atom)