SET SERVEROUTPUT ON


-- Demonstrate the usage of getChildTextNode() subprogram. This PL/SQL Block prints the text value of the node named 'TRANSPORT'

DECLARE
  

  xmltypedoc XMLType; 
  doc        DBMS_XMLDOM.domdocument;  -- XML DOM document.
  docelem    DBMS_XMLDOM.DOMElement;   -- XML DOM element.
  nodeValue  VARCHAR2(30);             -- Text value of the node.  


BEGIN 
   xmltypedoc := 
   xmltype('
   <DESTINATION city="sydney" type="wildlife" season="yes">    

     <TRANSPORT> AIR, TRAIN, SEA </TRANSPORT>
     <DISTANCE> 625KM </DISTANCE>
     <WEATHER> 30 DEGREE </WEATHER>
     <HOTELS Star="5" >
       <HOTEL>
         <NAME> ABC International </NAME>

         <FACILITY>JACUZZI, SAUNA</FACILITY>
         <ROOM_NOS>346</ROOM_NOS>
       </HOTEL>
     </HOTELS>
     <HOTELS Star="3"> 
       <HOTEL>
         <NAME> ALLIANCE International </NAME>

         <FACILITY>SPA, JACUZZI</FACILITY>
         <ROOM_NOS>120</ROOM_NOS>
       </HOTEL>
     </HOTELS>
   </DESTINATION>'); 

  -- Create a DOM document. 
  doc := dbms_xmldom.newDOMDocument (xmltypedoc); 

  -- Get document element. 

  docelem := dbms_xmldom.getDocumentElement( doc );

  -- Get the text value of the node which has 'TRANSPORT' as the node name.
  nodeValue := xdb_utilities.getChildTextNode (docelem,'TRANSPORT');

  -- Display the value of the node obtained.
  DBMS_OUTPUT.PUT_LINE('node value is :'||nodeValue);

END;

/