The best way in TSQL for finding xml for a node that does not exist

Question

We have a source file XMLthat addressnode has , and each node must have a zip_codenode at the bottom for verification. We received a file that did not pass the circuit check, because at least one node was missing in zip_code (there were several thousand addresses in the file).

We need to find elements that do not have a zip code, so we can restore the file and send an audit report to the source.

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

Indeed, this is an offer wherethat hurts me. I feel that I am dependent on the cast for the value of nullup 0, and it works, but I'm not sure what it should. I tried several options using the function .exist, but I could not get the correct result.

Answer 1

, <zip_code>, - :

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

Answer 2

, address, zip_code, XPATH, :

/addresses/address[zip_code]

, zip_code , zip_node, , text() :

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

---

EDIT:

, . , .

, address, zip_code, XPATH :

/addresses/address[not(zip_code)]