Oracle PL/SQL Tutorial/PL SQL Data Types/XMLType

Extract and display the URL

   <source lang="sql">

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.ru
 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></source>

XMLType.CreateXML

   <source lang="sql">

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.ru
 9         </url>
10      </fall>"));

1 row created. SQL> SQL> INSERT INTO myTable VALUES (2, SYS.XMLType.CreateXML(

 2     "<?xml version="1.0"?>
 3      <fall>
 4         <name>Au myTable</name>
 5         <county>USA</county>
 6         <state>MI</state>
 7         <url>
 8            http://your.ru
 9         </url>
10      </fall>"));

1 row created. SQL> SQL> INSERT INTO myTable VALUES (3, SYS.XMLType.CreateXML(

 2     "<?xml version="1.0"?>
 3      <fall>
 4         <name>myTable</name>
 5         <county>USA</county>
 6         <state>MI</state>
 7      </fall>"));

1 row created. SQL> SQL> SQL> SQL> SQL> drop table myTable; Table dropped. SQL></source>

XMLType.getStringVal

   <source lang="sql">

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.ru
 9         </url>
10      </fall>"));

1 row created.

SQL> SQL> CREATE INDEX by_name

 2     ON myTable f (
 3        SUBSTR(
 4           SYS.XMLType.getStringVal(
 5              SYS.XMLType.extract(f.fall,"/fall/name/text()")
 6           ),1,80
 7        )
 8     );

Index created. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>