CODEWITHSUNDEEP
sqlsql-serverxml
Pooli
Pooli

Reputation: 503

Expecting a null returned from XML.value() in SQL, but getting 0

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?

Upvotes: 3

Views: 3947

Answers (1)

marc_s
marc_s

Reputation: 755541

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.

Upvotes: 5

Related Questions