SQL XML Check if at-least one of node has value

Question

I am writing a stored procedure where I need to check if one of the XML field "EmployeeId" has value in it. It should return the first found value in xml nodes. For eg, in below example, it should return node with value 19.

  @Table NVARCHAR(MAX),
  DECLARE @xmlEmployees XML = CAST(@Table AS XML);

Following is xml structure

 <Employee>
    <EmployeeId></EmployeeId>
 </Employee>
 <Employee>
   <EmployeeId></EmployeeId>
 </Employee>
 <Employee>
   <EmployeeId>19</EmployeeId>
 </Employee>
 <Employee>
    <EmployeeId>21</EmployeeId>
 </Employee>

In the above structure, the query should return the node with value 19.

https://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column — Eray Balkanli, Feb 09 '18 at 17:58

score 2 · Accepted Answer · answered Feb 09 '18 at 18:10

2

Just use correct XPath expression:

DECLARE @xmlEmployees XML = '<Employee>
    <EmployeeId></EmployeeId>
</Employee>
<Employee>
    <EmployeeId></EmployeeId>
</Employee>
<Employee>
    <EmployeeId>19</EmployeeId>
</Employee>
<Employee>
    <EmployeeId>21</EmployeeId>
</Employee>';

SELECT @xmlEmployees.value('(//EmployeeId[text()])[1]', 'int')

answered Feb 09 '18 at 18:10

Salman A

262,204
82
430
521

Thanks, @salman, this works, however marking the first one as the answer. – Application Manager Feb 09 '18 at 18:43
@ApplicationManager But this is the better answer... The currently accepted answer will use `.nodes()` to get a derived table of all `` nodes, read **all of them** and pick the first. Quite some overhead... – Shnugo Feb 09 '18 at 20:02

score 1 · Answer 2 · answered Feb 09 '18 at 18:01

Given your example I used this in SQL Server 2014 and it returned the value of 19, I just selected the top 1 result, and if the xml node is empty and is of value type INT it seems SQL Server converts it to 0, so I added a WHERE clause to filter out the top 2 entries as their EmployeeId values equal zero:

 DECLARE @xDoc XML = '<Employee>
    <EmployeeId></EmployeeId>
 </Employee>
 <Employee>
   <EmployeeId></EmployeeId>
 </Employee>
 <Employee>
   <EmployeeId>19</EmployeeId>
 </Employee>
 <Employee>
    <EmployeeId>21</EmployeeId>
 </Employee>'


 SELECT TOP 1 a.doc.value('EmployeeId[1]', 'INT') AS [EmployeeId]
 FROM @xDoc.nodes('/Employee') as a(doc)
 WHERE a.doc.value('EmployeeId[1]', 'INT') <> 0

SQL XML Check if at-least one of node has value

2 Answers2