SQL Puzzle | Check Node existence in XML using SQL

In this puzzle you have to check whether a node exists in the XML or not

Please check the sample input and the expected output.

Sample Input

<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>

Expected Output

Use below script to create table and insert sample data into it.

--

DECLARE @XML AS XML = 
'<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>'

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--

DECLARE @XML AS XML = 
'<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>'

SELECT @XML.exist('(/Master/a)') OUTPUT

--

Output-1

--                            

OUTPUT
------
1

(1 row affected)


--

Note –

1. The exists method returns 1 or 0. If it returns 1 then the node is present in the XML. If the method returns then the node is not present in the XML.

2. This method needs a path to check for the specific node. If the path is also incorrect it will return 0.

3. This method will return NULL if the input is NULL

Remark from MS –

The exist() method returns 1 for the XQuery expression that returns a nonempty result. If you specify the true() or false() functions inside the exist() method, the exist() method will return 1, because the functions true() and false() return Boolean True and False, respectively. That is, they return a nonempty result). Therefore, exist() will return 1 (True), as shown in the following example:

Refer more from Microsoft from below URL-

Related XML Puzzles

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com