XML Oracle : identify existing but empty element

Question

i'm new to xml DB with Oracle and i'm starting to get my bearings around it but there is one thing that is important that i can't figure to do : i need to identify 1. if an element exist 2. if it is empty

unfortunatly the XMLExists() just mix the two answers.

a small part of my query :

SELECT case when XMLEXISTS('/' passing by ref c3.CLASS) then 1 else 0 end E_CLASS,        
       c3.CLASS      
FROM XML_TEST x,
   XMLTABLE ('/Data/EMPLOYER'
                    PASSING x.File_XML
                    COLUMNS DOSSIER           NUMBER(8)       PATH     'DOSSIER',
                            SUMMARY           XMLTYPE         PATH 'SUMMARY'
            ) e,
   XMLTABLE ('/SUMMARY'
                PASSING e.SUMMARY
                COLUMNS BEGINDATE          DATE            PATH 'BEGINDATE',                            
                        WORKER             XMLTYPE         PATH 'WORKER'
            ) c1,
   XMLTABLE ('/WORKER'
                PASSING c1.WORKER
                COLUMNS NRWORKER           NUMBER(7)       PATH 'NRWORKER',                            
                        RESULT_DETAIL      XMLTYPE         PATH 'RESULT_DETAIL'
            ) c2 ,
    XMLTABLE ('/RESULT_DETAIL'
                PASSING c2.RESULT_DETAIL
                COLUMNS CODE               CHAR(5)         PATH 'CODE',                            
                        MINUTES            NUMBER(5)       PATH 'MINUTES',                            
                        CLASS              CHAR(1)         PATH 'CLASS'                            
            ) c3 ;

kfinity · Accepted Answer · 2019-02-13 14:48:57Z

XMLEXISTS does return true for empty elements, you're just sending it c3.CLASS, which is a CHAR(1) column instead of an XMLTYPE, so it doesn't detect a root element (/) and returns false.

Here's an example of different ways you can play around with this. I added an XMLTYPE copy of CLASS to c3, and referenced it in the SELECT clause. Try deleting the "z" from the sample data, or the whole CLASS node, and see what happens.

See this similar question for how to write an XPATH query to check if a node is empty.

-- sample data
with xml_test as (select xmltype('<Data><EMPLOYER><SUMMARY><WORKER><RESULT_DETAIL><CLASS>z</CLASS></RESULT_DETAIL></WORKER></SUMMARY></EMPLOYER></Data>') as file_xml from dual)
-- your query
SELECT --case when XMLEXISTS('/' passing by ref c3.class) then 1 else 0 end E_CLASS, /* won't work if CLASS is non-empty */
       case when XMLEXISTS('/' passing by ref c3.class_x) then 1 else 0 end E_CLASS_X,
       case when XMLEXISTS('/RESULT_DETAIL/CLASS' passing by ref c2.RESULT_DETAIL) then 1 else 0 end E_RD_CLASS,
       c3.CLASS, 
       c3.CLASS_X, 
       -- but this probably does what you want, detect if CLASS exists and is empty:
       case when XMLEXISTS('/CLASS' passing by ref c3.class_x)
             and not XMLEXISTS('/CLASS/text()' passing by ref c3.class_x) then 1 else 0 end EMPTY_CLASS
FROM XML_TEST x,
   XMLTABLE ('/Data/EMPLOYER'
                    PASSING x.File_XML
                    COLUMNS DOSSIER           NUMBER(8)       PATH     'DOSSIER',
                            SUMMARY           XMLTYPE         PATH 'SUMMARY'
            ) e,
   XMLTABLE ('/SUMMARY'
                PASSING e.SUMMARY
                COLUMNS BEGINDATE          DATE            PATH 'BEGINDATE',                            
                        WORKER             XMLTYPE         PATH 'WORKER'
            ) c1,
   XMLTABLE ('/WORKER'
                PASSING c1.WORKER
                COLUMNS NRWORKER           NUMBER(7)       PATH 'NRWORKER',                            
                        RESULT_DETAIL      XMLTYPE         PATH 'RESULT_DETAIL'
            ) c2 ,
    XMLTABLE ('/RESULT_DETAIL'
                PASSING c2.RESULT_DETAIL
                COLUMNS CODE               CHAR(5)         PATH 'CODE',                            
                        MINUTES            NUMBER(5)       PATH 'MINUTES',                            
                        CLASS              CHAR(1)         PATH 'CLASS',
                        CLASS_X            XMLTYPE         PATH 'CLASS'  -- added an XMLTYPE column with the same data                          
            ) c3 ;

MT0 · Accepted Answer · 2019-02-13 15:41:10Z

You can use //[not(text())][not(*)] to find elements without any text and without children.

So in your context you could use:

CASE
WHEN c2.RESULT_DETAIL IS NULL
     OR
     XMLEXISTS(
       '/RESULT_DETAIL/CLASS[not(text())][not(*)]'
       PASSING c2.RESULT_DETAIL
     )
THEN 1
ELSE 0
END AS isClassEmpty

You could also LEFT OUTER JOIN your tables so that missing elements still appear and then you could just test to see if the value of CLASS is NULL:

Oracle Setup:

CREATE TABLE XML_TEST ( File_XML ) AS
SELECT XMLTYPE( '<Data>
  <EMPLOYER>
    <DOSSIER>1</DOSSIER>
    <SUMMARY>
      <BEGINDATE>2019-02-13</BEGINDATE>
      <WORKER>
        <NRWORKER>42</NRWORKER>
        <RESULT_DETAIL>
          <CODE>12345</CODE>
          <MINUTES>0</MINUTES>
          <CLASS></CLASS>
        </RESULT_DETAIL>
      </WORKER>
    </SUMMARY>
  </EMPLOYER>
</Data>' ) FROM DUAL UNION ALL
SELECT XMLTYPE( '<Data>
  <EMPLOYER>
    <DOSSIER>2</DOSSIER>
    <SUMMARY>
      <BEGINDATE>2019-02-14</BEGINDATE>
      <WORKER>
        <NRWORKER>1</NRWORKER>
        <RESULT_DETAIL>
          <CODE>98765</CODE>
          <MINUTES>600</MINUTES>
          <CLASS>B</CLASS>
        </RESULT_DETAIL>
      </WORKER>
    </SUMMARY>
  </EMPLOYER>
</Data>' ) FROM DUAL UNION ALL
SELECT XMLTYPE( '<Data>
  <EMPLOYER>
    <DOSSIER>3</DOSSIER>
    <SUMMARY>
      <BEGINDATE>2019-02-14</BEGINDATE>
      <WORKER>
        <NRWORKER>7</NRWORKER>
      </WORKER>
    </SUMMARY>
  </EMPLOYER>
</Data>' ) FROM DUAL

Query:

SELECT Dossier,
       BeginDate,
       NRWorker,
       Code,
       Minutes,
       Class,
       CASE
       WHEN c2.RESULT_DETAIL IS NULL
            OR
            XMLEXISTS(
              '/RESULT_DETAIL/CLASS[not(text())][not(*)]'
              PASSING c2.RESULT_DETAIL
            )
       THEN 1
       ELSE 0
       END AS isClassEmpty,
       CASE WHEN Class IS NULL THEN 1 ELSE 0 END AS isClassEmpty2
FROM XML_TEST x
     LEFT OUTER JOIN
     XMLTABLE(
       '/Data/EMPLOYER'
       PASSING x.File_XML
       COLUMNS DOSSIER  NUMBER(8) PATH 'DOSSIER',
               SUMMARY  XMLTYPE   PATH 'SUMMARY'
     ) e
     ON ( 1 = 1 )
     LEFT OUTER JOIN
     XMLTABLE(
       '/SUMMARY'
       PASSING e.SUMMARY
       COLUMNS BEGINDATE DATE    PATH 'BEGINDATE',                            
               WORKER    XMLTYPE PATH 'WORKER'
     ) c1
     ON ( 1 = 1 )
     LEFT OUTER JOIN
     XMLTABLE(
       '/WORKER'
       PASSING c1.WORKER
       COLUMNS NRWORKER      NUMBER(7) PATH 'NRWORKER',                            
               RESULT_DETAIL XMLTYPE   PATH 'RESULT_DETAIL'
     ) c2
     ON ( 1 = 1 )
     LEFT OUTER JOIN
     XMLTABLE(
       '/RESULT_DETAIL'
       PASSING c2.RESULT_DETAIL
       COLUMNS CODE    CHAR(5)   PATH 'CODE',                            
               MINUTES NUMBER(5) PATH 'MINUTES',                            
               CLASS   CHAR(1)   PATH 'CLASS'                          
     ) c3
     ON ( 1 = 1 );

Output:

DOSSIER | BEGINDATE | NRWORKER | CODE  | MINUTES | CLASS | ISCLASSEMPTY | ISCLASSEMPTY2
------: | :-------- | -------: | :---- | ------: | :---- | -----------: | ------------:
      1 | 13-FEB-19 |       42 | 12345 |       0 | null  |            1 |             1
      2 | 14-FEB-19 |        1 | 98765 |     600 | B     |            0 |             0
      3 | 14-FEB-19 |        7 | null  |    null | null  |            1 |             1

db<>fiddle here

Collectives™ on Stack Overflow

XML Oracle : identify existing but empty element

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related