As Yithzak says, as long as we don't have a full example of what you want to do, we can't be sure. But this seems to work with this simple example. (I took Yithzak's example and made some modifications. Using node
rather than
text seems to do the trick.
-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, XMLBlobXML XML);
INSERT INTO @tbl (XMLBlobXML) VALUES
(N'<ContractProviderFile>
<networkProviderRecord>
<CareProviderTracerIDs>
<careProviderTracerID>
<TracerID>N2221803_1730173873</TracerID>
<TracerSourceID>2221803</TracerSourceID>
<TracerSourceType>NDB</TracerSourceType>
</careProviderTracerID>
</CareProviderTracerIDs>
<ExternalProviderNumbers>
<ExternalProviderNumber>NY0000075</ExternalProviderNumber>
</ExternalProviderNumbers>
<IndivProvider>
<BusinessPartnercode>BP008</BusinessPartnercode>
<BusinessPartnerdesc>UHN PROFESSIONAL</BusinessPartnerdesc>
<dEANumbers>
<dEANumber>BR6799184</dEANumber>
</dEANumbers>
</IndivProvider>
</networkProviderRecord>
</ContractProviderFile>'),
(N'<ContractProviderFile>
<networkProviderRecord>
<IndivProvider>
<BusinessPartnercode>BP008</BusinessPartnercode>
<BusinessPartnerdesc>UHN PROFESSIONAL</BusinessPartnerdesc>
<dEANumbers>Happy New Year!</dEANumbers>
</IndivProvider>
</networkProviderRecord>
</ContractProviderFile>'),
(N'<ContractProviderFile>
<networkProviderRecord>
<IndivProvider>
<BusinessPartnercode>BP008</BusinessPartnercode>
<BusinessPartnerdesc>UHN PROFESSIONAL</BusinessPartnerdesc>
<dEANumbers />
</IndivProvider>
</networkProviderRecord>
</ContractProviderFile>');
-- DDL and sample data population, end
SELECT *
FROM @tbl
WHERE XMLBlobXML.exist('/ContractProviderFile/networkProviderRecord/IndivProvider/dEANumbers[not(node())]') = 1;