Re: How to Check Value of XMLType ?

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 14 May 2007 00:04:42 -0700
Message-ID: <1179126281.985871.102060@k79g2000hse.googlegroups.com>

On May 12, 6:04 am, "narino" <Nar..._at_mailinator.com> wrote:
> 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..._at_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

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.OShea_at_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> Received on Mon May 14 2007 - 02:04:42 CDT