SQL XML检查至少有一个节点是否有值

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.

我正在编写一个存储过程，我需要检查其中一个XML字段“EmployeeId”是否具有值。它应该返回xml节点中的第一个找到的值。例如，在下面的示例中，它应该返回值为19的节点。

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

Following is xml structure

以下是xml结构

 <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.

在上面的结构中，查询应返回值为19的节点。

2 个解决方案

#1

Just use correct XPath expression:

只需使用正确的XPath表达式：

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

#2

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:

鉴于你的例子，我在SQL Server 2014中使用它，它返回值19，我刚刚选择了前1个结果，如果xml节点为空且值为INT类型，似乎SQL Server将其转换为0，所以我添加了一个WHERE子句来过滤掉前2个条目，因为它们的EmployeeId值等于零：

 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