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’

Advertisements

About Vijay Modi

Having 12+ years of experience in web application development. Expertise in various domains like E-Commerce, E-Learning, Insurance. I have expertise in web application development, window application development, Performance improvement, bug fixing etc. I have worked on various Microsoft technologies including ASP.Net, MVC, WebApi, WCF, Entity Framework, WPF, Window Application Development, AWS, Azure, AngularJS 2.0 / 1.5.2, .NetCore framework, jQuery. I am believing in quality work and achieving deadlines. Also like to work on new technologies and quick learner.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

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

  1. sandrar says:

    Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.

  2. forestGreen says:

    Do you have any idea how to check that nodes are empty?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s