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.

DECLARE @XmlDoc XML

SET @XmlDoc = ‘’
DECLARE @docHandle int

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

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