CODEWITHSUNDEEP
sql-serverxmlsql-server-2008-r2xquery
mhopkins321
mhopkins321

Reputation: 3073

Find all "null" values using xquery

I was tasked to find all values where <nodeA> is empty. That could mean that it is NULL, or that it doesn't exist.

I have crafted the below query

SELECT *
FROM table
WHERE AttributeList.value ('(/AttributeList/nodeA)[1]', 'varchar(50)') IS NULL

However it returns only results that look like <AttributeList />

What I am curious of is would this also return an XML that looked like

<AttributeList>
    <nodeA></nodeA>
</AttributeList>

Upvotes: 1

Views: 5014

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

There is no need to extract the values from the XML. Use exist instead.

select *
from YourTable 
where AttributeList.exist('/AttributeList/nodeA/node()') = 0

Upvotes: 1

Jens Erat
Jens Erat

Reputation: 38662

Use fn:empty(...) to check whether there are any children. Remember to handle both attributes and other nodes (elements, text nodes, ...) if needed.

//*[empty( (./node(), ./attribute()) )]

W3schools.com has a nice overview on axis steps if you want to redefine "empty".

Upvotes: 0

Related Questions