10gR2

I get a XMLType fragment from a SELECT like:

SELECT xmlelement("people",....)
     INTO l_xmltype
   FROM dual;

Result is either like this for example, a "full" chunk of XML:

<people>
  <author>
    <firstname>Robert</firstname>
    <surname>Frost</surname>
    <address>
      <email>x@xxx.xxx</email>
    </address>
  </author>
</people>

Or when no records found:

<people></people>

--------------------------------------------------

I want to check what type of result I get :

IF l_xmltype = '<people></people>' THEN
    DBMS_OUTPUT.PUT_LINE('no record found');
ELSE
   --- continue on with whatever
END IF;

How to do this ?

Thanks

On May 12, 6:04 am, "narino" <Nar@mailinator.com> wrote:

Hi, just see whether the author node exists with an xpath expression.
See
the example below using method EXISTSNODE - returns 1 when the node
exists, 0 otherwise.

Regards
Mike

TESSELLA   Michael.OS@tessella.com
__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301
www.tessella.com    Registered in England No. 1466429

SQL>
SQL>
SQL> SELECT
XMLTYPE('<people><author><firstname>foo</firstname><lastname>baa</
lastname></author></people>').EXISTSNODE('//people/author')
xpathAuthor1,
XMLTYPE('<people></people>').EXISTSNODE('//people/author')
xpathAuthor2
FROM DUAL;

SELECT *
FROM V$VERSION;  2    3
XPATHAUTHOR1 XPATHAUTHOR2
------------ ------------
1            0

SQL> SQL>   2

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>