[SOLVED] SQL XML Query/Exist: Looking for null/not null values

SQL XML Query/Exist: Looking for null/not null values

SQL Server 2016
Field1 (XML(.), null)

Example data:

<ProcessPositionOpening xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.hr-xml.org/3">
    <ApplicationArea xmlns="http://www.openapplications.org/oagis/9">
        ...
    </ApplicationArea>
    <DataArea>
        <Process xmlns="http://www.openapplications.org/oagis/9">
            ...
        </Process>
        <PositionOpening>
            <DocumentID />
            <PositionRequester>
                ...
            </PositionRequester>
            <PositionProfile>
                ...
                <PositionFormattedDescription>
                    <ID>...</ID>
                    <Content />
                </PositionFormattedDescription>
                ...
            </PositionProfile>
        </PositionOpening>
    </DataArea>
</ProcessPositionOpening>

In this table, Field1 is setup as XML and I have been trying to find all records where the node Content may not be NULL. However, I've tried different examples of query and exist, but can't seem to get the query right. Here is the current one I'm trying, but it's not returning what I expected:

SELECT  *
FROM    Table1
WHERE   Field1.value('(/ProcessPositionOpening/DataArea/PositionOpening/PositionProfile/PositionFormattedDescription/Content)[1]','varchar(50)') <> ''
    AND Message = 'Request Received';

I've also tried:

SELECT  *
FROM    Table1
WHERE   Message = 'Request Received'
    AND Field1.exist('/ProcessPositionOpening') = 1;

I tried the query above just to see if the basic setup of the query would work. It returns no records. If I remove the AND part of the query, I have over 19,000 rows in this table that are all in this format. If I change the 1 to a 0 in the AND part of the query, it returns records, but I feel that's wrong as I thought (based on pages explaining the exist function, 0 means NOT exist). So, I'm confused.

I want to find all records where the Content node has a value, and likewise find all where Content does NOT have a value. Any help is truly appreciated. Thanks!

Solution

I hope the following provides some templates how to solve this:

DECLARE @mockup TABLE(Descr VARCHAR(100),theXML XML);
INSERT INTO @mockup VALUES
 ('<content> exists with value','<root><content>blah</content></root>' )
,('<content> exists without value','<root><content></content></root>' ) --semantically the same as self-closed...
,('<content> exists self-closed','<root><content/></root>' )
,('<content> does not exist','<root></root>' );

--Find a Content with value
SELECT * FROM @mockup
WHERE theXML.exist('/root/content/text()')=1;

--Find a Content with or without value
SELECT * FROM @mockup
WHERE theXML.exist('/root/content')=1;

--Find a Content without value
SELECT * FROM @mockup
WHERE theXML.exist('/root/content[empty(text())]')=1;

--Find rows with no content
SELECT * FROM @mockup
WHERE theXML.exist('/root/content')=0;