How to check if a given node exists in XML document

Question

How to check if given node exists in XML document?

XML example:

<order>
    <desc>
        <name>Test name</name>
        <code>Test code</code>
    </desc>
    <suborders>
        <item>
            <id>1000</id>
        </item>
        <item>
            <id>2000</id>
        </item>
    </suborders>
    <options>
       <item/>
    </options>
</order>

How to check in PL/SQL if any suborder's item exists?

I tried like this:

DECLARE
   myxml CLOB := ...
BEGIN 
   SELECT extractValue(XMLTYPE(myxml), '/order/suborders/item[1]') 
     INTO firstSuborder 
    FROM DUAL;

   IF (firstSuborder IS NULL) THEN
          dbms_output.put_line('suborder doesnt exist');
   END IF;
END;

but I got ORA-19025: EXTRACTVALUE returns value of only one node.

Francesco Genta · Accepted Answer · 2014-11-07 08:25:43Z

2

You can simply use existsnode, here documentation http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#i1032763

declare
  OBJECT_VALUE xmltype := xmltype(q'{<order>
    <desc>
        <name>Test name</name>
        <code>Test code</code>
    </desc>
    <suborders>
        <item>
            <id>1000</id>
        </item>
        <item>
            <id>2000</id>
        </item>
    </suborders>
    <options>
       <item/>
    </options>
</order>}');

  node_exists pls_integer;

begin

  select existsNode(OBJECT_VALUE, '/order/suborders/item[1]') into node_exists from dual;
  dbms_output.put_line('exists: ' || to_char(node_exists));
end;

answered Nov 7, 2014 at 8:25

Francesco Genta

3331 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to check if a given node exists in XML document

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related