Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
Hello,
I trying to use XMLEXISTS to find out if a node, 'COMMENTS', is in the XML.
The XML is stored in a clob in a table.
In a Function in a Package I'm retrieving the clob and converting it into an XMLTYPE using:
get_results_xmltype := xmltype.createxml (get_results_clob);
The XML may or may not contain this 'COMMENTS' node so I thought I could do this:
|
SELECT CASE WHEN xmlexists('Comments' PASSING get_results_xmltype) THEN 1 ELSE 0 END |
|
INTO v_comments |
|
FROM XMLTABLE |
| |
('/Comments/Comment' PASSING get_results_xmltype |
| |
COLUMNS |
| |
comment_col VARCHAR2(4000) PATH 'Comment'); |
v_comments is defined as a NUMBER.
I'm then evaluating v_comments with an IF statement.
If the 'COMMENTS' does exist then I'm inserting the data into a staging table (along with other fields of course) and if it doesn't I'm inserting NULL for that field into the staging table (along with other fields of course).
My above attempt does not find the 'COMMENTS' in the XML and is always returning 0 into v_comments when the 'COMMENTS' does exist in the XML. So I'm obviously not doing this correctly.
Can someone help me with how to do this?
What additional information can I provide for this?
Thanks,
Joe