ExistsNode - What's wrong

Question

I create a table in oracle like this:

CREATE TABLE XML_TEMP (
          XML_DATA CLOB
);

INSERT INTO XML_TEMP(XML_DATA) VALUES('
    <envCFe versao="0.07" xmlns="http://www.fazenda.sp.gov.br/sat">
        <tpAmb>1</tpAmb>
        <idLote>4095</idLote>
        <cUF>35</cUF>
        <LoteCFe>    
        </LoteCFe>
        <nSeg></nSeg>
        <dhEnvio>20171101101517</dhEnvio>
    </envCFe>
                                  ');

When I select the existsNode return 0

SELECT ExistsNode(XMLTYPE(XML_DATA), '/envCFe') HAS, XML_DATA FROM XML_TEMP;

I don't understang what's wrong, someone could help.

The link http://sqlfiddle.com/#!4/c2b9e/5/0 has the SQLFiddle

Answer 1

Your root node has a namespace, so you need to specify that using the optional third argument:

SELECT ExistsNode(XMLTYPE(XML_DATA), '/envCFe',
    'xmlns="http://www.fazenda.sp.gov.br/sat"') HAS, XML_DATA
FROM XML_TEMP;

       HAS XML_DATA                                                                        
---------- --------------------------------------------------------------------------------
         1                                                                                 
               <envCFe versao="0.07" xmlns="http://www.fazenda.sp.gov.br/sat">             
                   <tpAmb>1</tpAmb>                                                        
                   <idLote>4095</idLote>                                                   
                   <cUF>35</cUF>                                                           
                   <LoteCFe>                                                               
                   </LoteCFe>                                                              
                   <nSeg></nSeg>                                                           
                   <dhEnvio>20171101101517</dhEnvio>                                       
               </envCFe>                                                                   

SQL Fiddle

If you intend to use ExistsNode to filter your results, and since that function is deprecated, you could use the XMLExists operator for that scenario instead:

SELECT XML_DATA
FROM XML_TEMP
WHERE XMLExists(
  'declare namespace ns="http://www.fazenda.sp.gov.br/sat"; (: :)
  /ns:envCFe'
  PASSING XMLTYPE(XML_DATA)
);

SQL Fiddle

You can't get the result of that as part of a query result though, as your current query is doing.