Chapter 26: Getting Started with Oracle XML and PL/SQL

Last Update: April 23, 2004

The examples in this chapter demonstrates the basic techniques on how to use Oracle’s PL/SQL XML components, including DBMS_XMLPARSER, DBMS_XMLDOM, DBMS_XSLPROCESSOR to parse XML documents, edit XML using DOM, transform and process XML data using XSLT. Additionally, a script for installing Oracle JVM is included.

Example 1: Setting up the Environment
Example 2: Parsing XML using UTL_FILE_DIR
Example 3: Parsing XML using BFILE
Example 4: Parsing XML using HTTPUriType
Example 5: Parsing XML using XDBUriType
Example 6: Parsing XML using HTTP Proxy
Example 7: Parsing XML by Setting the User Authentications
Example 8: Creating XML Element with Namespaces
Example 9: Retrieving XML Data using XPath
Example 10: Retrieving XML Data using XPath

Example 1: Setting up the Environment

1. Login SQL*Plus as SYS user and create the demo user for running the examples:

> sqlplus "sys/oracle as sysdba" 
SQL> grant connect, resource to demo identified by demo;

2. Check the installation of the XML PL/SQL packages by logging in as demo. For example, in order to check the DBMS_XMLPARSER package, you can use the following command:

> sqlplus demo/demo 
SQL> DESC dbms_xmlparser

If you see the PL/SQL procedure defintations on the screen, you can skip the following step.

3. Login to SYS user to check if there are XML PL/SQL packages installed and who is the owner of the packages by issuing the following command:

> sqlplus "sys/oracle as sysdba"
SQL> SELECT UNIQUE(owner), object_name
FROM DBA_PROCEDURES
WHERE object_name IN ('DBMS_XMLPARSER', 'DBMS_XSLPROCESSOR', 'DBMS_XMLDOM')

If the XML packages are installed, the result you get will be like this:

OWNER                   OBJECT_NAME
-------------------------------------------------------------
  XDB                   DBMS_XMLDOM
  XDB                   DBMS_XMLPARSER
  XDB                   DBMS_XSLPROCESSOR

Then you just need to grant the execution previleges to demo user. Otherwise, you need to install the package by runing as follows:

@@dbmsxmld.sql
@@dbmsxmlp.sql
@@dbmsxslp.sql
@@prvtxmld.plb
@@prvtxmlp.plb
@@prvtxslp.plb

Back to Top

Example 2: Parsing XML using UTL_FILE_DIR

UTL_FILE_DIR is one of the initilaization parameters for the Oracle database. You can define one or multiple directory paths within UTL_FILE_DIR to allow database users to read or write files from those directories on the databaser server. This example shows how you can use it and parse XML files with DBMS_XMLPARSER.

1. Login SQL*Plus as SYS user and run the following command:

> sqlplus "sys/oracle as sysdba" 
SQL> ALTER SYSTEM
SET UTL_FILE_DIR='D:\xmlbook\Examples\Chapter26\src\xml' SCOPE=SPFILE;

Please update the directory path if needed. If you want to specify multiple directory path you an runt he command like this:

ALTER SYSTEM SET UTL_FILE_DIR='directory1','directory2' SCOPE=SPFILE; 

After this, you can check the update by issuing the following command:

SQL> show parameters utl_file_dir
NAME          TYPE     VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string D:\jiwang\work_xmlbook\Examples\Chapter26\src\xml

You need to restart the database to be able to use the new setups of the initialization parameter.

2. Go to the $EXAMPLE_HOME/Chapter26/src/sql and open an SQL*Plus session connecting to the demo can run the following command:

>sqlplus demo/demo
>@parseXML_UTL_FILE_DIR.sql

Back to Top

Example 3: Parsing XML using BFILE

1. Go to the $EXAMPLE_HOME/Chapter26/src/sql. Update the directory in the createDirectory.sql to reflect the directory of your $EXAMPLE_HOME/Chapter26/src/xml. Login SQL*Plus as SYS user and run the following command:

> sqlplus "sys/oracle as sysdba" 
SQL> @createDirectory.sql

2. Connect to demo user and run the following command:

> sqlplus demo/demo
> set echo on
SQL> @createBFILEtoCLOB_proc.sql SQL> @parseXML_BFILE.sql

Back to Top

Example 4: Parsing XML using HTTPUriType

1. Go to the $EXAMPLE_HOME/Chapter26/src/sql. Connect to demo user and run the following command:

> sqlplus demo/demo
SQL> set echo on 
SQL> set serveroutput on size 100000
SQL>@createPrintClob_proc.sql
SQL>@parseXML_HTTPUriType.sql

Back to Top

Example 5: Parsing XML using XDBUriType

1. Please copy the file author_xdb.xml and author.dtd in $EXAMPLE_HOME/Chapter26/src/xml to /public directory in the XML DB reposiotry using either WebDAV or FTP interfaces as we discussed in Chapter 9.

2. Go to the $EXAMPLE_HOME/Chapter26/src/sql. Connect to demo user and run the following command:

> sqlplus demo/demo
SQL> set echo on 
SQL> set serveroutput on size 100000
SQL>@parseXML_XDBUriType.sql

Back to Top

Example 6: Parsing XML using HTTP Proxy

1. Go to the $EXAMPLE_HOME/Chapter26/src/sql. Connect to demo user and run the following command:

> sqlplus demo/demo
SQL> set echo on 
SQL> set serveroutput on size 100000
SQL>@parseXML_PROXY.sql

You should see that the following errors will be shown if the proxy is not set:

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.HTTPURITYPE", line 34
ORA-06512: at "SYS.HTTPURITYPE", line 97
ORA-06512: at line 6

Back to Top

Example 7: Parsing XML by Setting the User Authentications

1. Go to the $EXAMPLE_HOME/Chapter26/src/sql. Connect to demo user and run the following command:

> sqlplus demo/demo
SQL> set echo on 
SQL> set serveroutput on size 100000
SQL>@parseXML_AuthURL.sql

2. After this run the following SQL scripe to try a different approach by logging in as demo user:

> sqlplus demo/demo
SQL> set echo on 
SQL> set serveroutput on size 100000
SQL>@parseXML_AuthHTTP.sql

Because we parse the contact.xml stored in /public directory of the XML DB repository.
Please make sure the files is there. Otherwise, you can use FTP or WebDAV interface to
copy the file to the /public directory.

Back to Top

Example 8: Creating XML Element with Namespaces

1. Go to the $EXAMPLE_HOME/Chapter26/src/sql. Connect to demo user and run the following command:

> sqlplus demo/demo
SQL> set echo on 
SQL> set serveroutput on size 100000
SQL>@createNSElement.sql

Back to Top

Example 9: Retrieving XML Data using XPath

1. Go to the $EXAMPLE_HOME/Chapter26/src/sql. Connect to demo user and run the following command:

> sqlplus demo/demo
SQL> set echo on 
SQL> set serveroutput on size 100000
SQL>@selectNodes.sql

Back to Top

Example 10: Retrieving XML Data using XPath

1. Go to the $EXAMPLE_HOME/Chapter26/src/sql. Connect to demo user and run the following command:

> sqlplus demo/demo
SQL> set echo on 
SQL> set serveroutput on size 100000
SQL>@processXML_XSLT.sql

Copyright © 2003