Extract and display the URL : XMLType « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. XMLType

SQL>
SQL> CREATE TABLE myTable (
  2     fall_id NUMBER,
  3     fall SYS.XMLType
  4  );

Table created.

SQL>
SQL> INSERT INTO myTable VALUES (1, SYS.XMLType.CreateXML(
  2     '<?xml version="1.0"?>
  3      <fall>
  4         <name>myTable</name>
  5         <county>USA</county>
  6         <state>MI</state>
  7         <url>
  8            http://your.com
  9         </url>
 10      </fall>'));

1 row created.



SQL>
SQL>
SQL> <<demo_block>>
  2  DECLARE
  3     fall SYS.XMLType;
  4     url VARCHAR2(80);
  5  BEGIN
  6
  7     SELECT fall INTO demo_block.fall
  8     FROM myTable f
  9     WHERE f.fall_id = 1;
 10
 11     url := fall.extract('/fall/url/text()').getStringVal;
 12     DBMS_OUTPUT.PUT_LINE(url);
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>