XML Oracle:标识现有但为空的元素 [英] XML Oracle : identify existing but empty element

问题描述

我是使用Oracle的xml DB的新手，并且我开始了解它，但是有一件重要的事情我无法确定:我需要确定 1.如果一个元素存在 2.如果为空

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

不幸的是XMLExists()只是混合了两个答案.

unfortunatly the XMLExists() just mix the two answers.

查询的一小部分:

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 ;

推荐答案

XMLEXISTS对于空元素确实返回true，您只是将其发送给c3.CLASS，它是CHAR(1)列而不是XMLTYPE ，因此它不会检测到根元素(/)并返回false.

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.

这是您可以使用此方法的不同示例.我将CLASS的XMLTYPE副本添加到c3，并在SELECT子句中引用了它.尝试从示例数据或整个CLASS节点中删除"z"，然后看看会发生什么.

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.

对于如何编写XPATH查询以检查节点是否为空.

-- 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 ;

这篇关于XML Oracle:标识现有但为空的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！