How to find rows with empty XML tag

Naomi Nosonovsky 8,146 Reputation points
2024-01-16T21:02:18.8066667+00:00

Hi, I have an XML like this (start of XML only):

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

I want to find records with empty tags inside XML. I tried

SELECT TOP (100) * FROM consume_tbl.PMFChangeLogTemp_CareProvider
WHERE XMLBlobXML.exist('/ContractProviderFile/networkProviderRecord/IndivProvider/dEANumbers[not (text())]') = 1

but it didn't seem to work as it returned rows containing empty and not empty dEANumbers tag. How can I get it to return rows with that tag empty? Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,482 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 117.1K Reputation points MVP
    2024-01-16T22:18:20.3333333+00:00

    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;
    

1 additional answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 26,376 Reputation points
    2024-01-16T21:32:15.49+00:00

    Hi @Naomi Nosonovsky, Please try the following solution.

    -- 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/>
    		</IndivProvider>
    	</networkProviderRecord>
    </ContractProviderFile>'),
    (N'<ContractProviderFile>
    	<networkProviderRecord>
    		<IndivProvider>
    			<BusinessPartnercode>BP008</BusinessPartnercode>
    			<BusinessPartnerdesc>UHN PROFESSIONAL</BusinessPartnerdesc>
    			<dEANumbers>Happy New Year!</dEANumbers>
    		</IndivProvider>
    	</networkProviderRecord>
    </ContractProviderFile>');
    -- DDL and sample data population, end
    
    SELECT * 
    FROM @tbl
    WHERE XMLBlobXML.exist('/ContractProviderFile/networkProviderRecord/IndivProvider/dEANumbers[not(text())]') = 1;
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.