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

Question

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!

Hint: Your XML has got namespaces. What makes things worse: There are different default namespaces. You can either declare them properly (read about WITH XMLNAMESPACES) or you can omit them using wildcards (/*:root/*:content)... — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 9, 2018 at 17:53
@Shnugo - That appears to have worked. I was trying to work with your answer, but was still not getting any results. Adding in the *: to the text worked. — XstreamINsanity
– XstreamINsanity, Commented Mar 9, 2018 at 18:16

Gottfried Lesigang · Accepted Answer · 2018-03-09 16:43:05Z

2

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;

answered Mar 9, 2018 at 16:43

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kristoffer_o · Accepted Answer · 2020-08-10 09:10:04Z

The xml.exist function is not intuitive to me and I've failed quite often using it. Even though the documentation clearly states how to use it: https://learn.microsoft.com/en-us/sql/t-sql/xml/exist-method-xml-data-type?view=sql-server-ver15

But to read the documentation beforehand is not intuitive to me either :)

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>' )
,('XML is null', NULL );
SELECT *, theXML.exist('/root/content') FROM @mockup

Gives

<content> exists with value <root><content>blah</content></root>    1
<content> exists without value  <root><content /></root>    1
<content> exists self-closed    <root><content /></root>    1
<content> does not exist    <root />    0
XML is null NULL    NULL

So I always wrap the condition in ISNULL(..,..)

SELECT *, ISNULL(theXML.exist('/root/content'),0) FROM @mockup

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related