Parsing XML Documents Using XPath In Oracle

Oracle has XMLTYPE data type to work on XML objects inside database. (For more information please visit otn ) You can parse XML documents using XPath queries(For more information please visit w3 ) I demonstrate a simple example to show how to parse XML documents witj PL/SQL


DECLARE
  orginal_xml xmltype;
  extracted_xml xmltype;
BEGIN
  orginal_xml := xmltype.createxml(
                  '
                   
                        Roberto Carlos
                   
                   
                        Arsene Lupin
                   
                   
                        Elvis Presley
                   
                  '
                  );
  dbms_output.put_line( 'Orginal XML :' );
  dbms_output.put_line(orginal_xml.getStringVal());
 
  dbms_output.put_line( lpad('-',100, '-' ) );
  extracted_xml := orginal_xml.extract('//students/student[ position() = 1 ]');
  dbms_output.put_line( 'XPath Expression : ' || '//students/student[ position() = 1 ] '  );
  dbms_output.put_line( extracted_xml.getStringVal() );
 
  dbms_output.put_line( lpad('-',100, '-' ) );
  extracted_xml := orginal_xml.extract('//students/student[ position() = last() ]/name/attribute::id');
  dbms_output.put_line( 'XPath Expression : ' || '//students/student[ position() = last() ]/name/attribute::id'  );
  dbms_output.put_line( extracted_xml.getStringVal() );
END;
The output is

Orginal XML :
   
        Roberto Carlos
   
   
        Arsene Lupin
   
   
        Elvis Presley
   
   

----------------------------------------------------------------------------------------------------
XPath Expression : //students/student[ position() = 1 ]

  Roberto Carlos

----------------------------------------------------------------------------------------------------
XPath Expression : //students/student[ position() = last() ]/name/attribute::id
1899