Tuesday, June 30, 2009

How to check xml parameter’s node value is null or empty in MS Sql

Suppose you are receving XML datatype of message in your stored procedure and you need to extract value from
that xml parameter. Before extracting data you need to check the node is exist or not. Means is that node is empty or not.
You can do it by using the .exist() in MS Sql.

Let me explain using an example.

Suppose we have a @XmlDoc and it contains the ‘’ data.
Here in example we are checking is there any Student contains Maths subject. As you can see in our Xml, it is there so it will return 1. Like the
same way we can check the particular node is exist in xml or not. So to check the value, we can use this way.


SET @XmlDoc = ‘
DECLARE @docHandle int

IF (@XmlDoc.exist(‘/Student/Subject/Maths’)=1)
PRINT ‘Maths Exist’
PRINT ‘Maths Not Exist’

IF (@XmlDoc.exist(‘/Student/Subject/Science’)=1)
PRINT ‘Scienct Exist’
PRINT ‘Science Not Exist’

No comments:

Post a Comment