Expecting a null returned from XML.value() in SQL, but getting 0
sql, sql-server, xml
Solution
If you change your XQuery to something like this:
SELECT @SearchFilter.value('(/KeywordSearch/SelfEmployed/text())[1]', 'varchar(100)')
then you'll get a `NULL` value back. Since you're checking to see whether or not the `<SelfEmployed>` node has any textual value, you should be interpreting the `.value()` as a string type - I'm using `varchar(100)` here - adapt as needed.
Problem
I'm attempting to read a specific value from an xml parameter passed into a stored procedure. A rough example of my code so far is below. ``` SET @SearchFilter = '<?xml version="1.0"?> <KeywordSearch xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <SelfEmployed xsi:nil="true" /> </KeywordSearch>' SET @SelfEmployed = @SearchFilter.query('/KeywordSearch/SelfEmployed[1]').value('/', 'bit') SELECT @SelfEmployed ``` The issue I'm having is that, although the above correctly returns when the element contains a true or false value, where the element contains no value, and thus I'm expecting null, it returns false. Is there a way to correctly obtain the datatype as a nullable bit value?