TSQL 中搜索 xml 查找不存在节点的更好方法

Question

我们有一个源 XML 文件，其中包含一个 address 节点，并且每个节点下面应该有一个 zip_code 节点以便进行验证。我们收到一个未通过架构验证的文件，因为至少有一个节点缺少 zip_code（文件中有数千个地址）。

我们需要找到没有邮政编码的元素，以便我们可以修复文件并向源发送审核报告。

--declare @x xml = bulkcolumn from openrowset(bulk 'x:\file.xml',single_blob) as s
declare @x xml = N'<addresses>
    <address><external_address_id>1</external_address_id><zip_code>53207</zip_code></address>
    <address><external_address_id>2</external_address_id></address>
</addresses>'

declare @t xml = (
select @x.query('for $a in .//address 
    return 
        if ($a/zip_code) 
            then <external_address_id /> 
        else $a/external_address_id')
)
select x.AddressID.value('.', 'int') AddressID
from @t.nodes('./external_address_id') x(AddressID)
where x.AddressID.value('.', 'int') > 0
GO

确实，这是 where 子句让我烦恼。我觉得我依赖于将 null 值强制转换为 0，并且它有效，但我不太确定它应该这样做。我尝试了 .exist 函数的一些变体，但无法获得正确的结果。

Answer 1

如果您只是想确保选择具有 zip_code 元素的 address 元素，则调整您的 XPATH 以在谓词过滤器中包含该条件：

/addresses/address[zip_code]

如果您还想确保 zip_code 元素也有一个值，对 zip_node 使用谓词过滤器来选择那些具有 text() 节点的节点：

/addresses/address[zip_code[text()]]

---

编辑：

其实我正在寻找
对面的。我需要识别节点
没有拉链，所以我们可以
手动更正源数据。

因此，如果您想识别所有没有 zip_code 的 address 元素，您可以在 XPATH 中指定它，如下所示：

/addresses/address[not(zip_code)]

Answer 2

如果您只想找到那些缺少 元素的节点，您可以使用如下内容：

SELECT
    ADRS.ADR.value('(external_address_id)[1]', 'int') as 'ExtAdrID'
FROM
    @x.nodes('/addresses/address') as ADRS(ADR)
WHERE
    ADRS.ADR.exist('zip_code') = 0

它使用内置的 .exist() 方法在 XQuery 中检查 XML 节点内是否存在子节点。