sqlsql-serverxmlsqlxml

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;