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.

Answer 1

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')