Filter an XML column in SQL Server

Question

I have an xml type column productsXML in product table

Create Table Product
(
    ProductId Int,
    productsXML XML
)

Insert Into Product 
Values (1, '<products><productInfo><Item>Car</Item></productInfo></products>'),
       (2, '<products><productInfo><Item>Train</Item></productInfo></products>'),
       (3, '<products></products>')

ProductId productsXML
----------------------------------------------------------------------------
1         <products><productInfo><Item>Car</Item></productInfo></products>
2         <products><productInfo><Item>Train</Item></productInfo></products>
3         <products></products>

I want to find all the rows that have <products></products>.

I tried this:

Select *
From products
Where productsxml.exist('/products') = 1

This is returning all the rows that have products tag and understandably so.

Is there a way to filter only those rows that have <products></products>?

Answer 1

PLease try the following solution.

It is checking that the root products element has no child elements.

SQL

DECLARE @tbl TABLE (ProductId int primary key, productsXML XML);
INSERT INTO @tbl (ProductId, productsXML) VALUES
(1,'<products><productInfo><Item>Car</Item></productInfo></products>'),
(2,'<products><productInfo><Item>Train</Item></productInfo></products>'),
(3,'<products></products>');

SELECT * 
FROM @tbl
Where productsxml.exist('/products[not(*)]')=1

Output

+-----------+--------------+
| ProductId | productsXML  |
+-----------+--------------+
|         3 | <products /> |
+-----------+--------------+