8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
External Tables and XMLTAG to Load XML Documents in Oracle Database 12c Release 2 (12.2)
This article describes how to use the XMLTAG clause of an external table to load XML fragments from XML documents in Oracle Database 12c Release 2 (12.2).
Related articles.
Setup
Create a file called "/tmp/test1.xml" with the following contents.
<employees> <employee><employee_number>7369</employee_number><employee_name>SMITH</employee_name><job>CLERK</job></employee> <employee><employee_number>7499</employee_number><employee_name>ALLEN</employee_name><job>SALESMAN</job></employee> <employee><employee_number>7521</employee_number><employee_name>WARD</employee_name><job>SALESMAN</job></employee> <employee><employee_number>7566</employee_number><employee_name>JONES</employee_name><job>MANAGER</job></employee> <employee><employee_number>7654</employee_number><employee_name>MARTIN</employee_name><job>SALESMAN</job></employee> <employee><employee_number>7698</employee_number><employee_name>BLAKE</employee_name><job>MANAGER</job></employee> <employee><employee_number>7782</employee_number><employee_name>CLARK</employee_name><job>MANAGER</job></employee> <employee><employee_number>7788</employee_number><employee_name>SCOTT</employee_name><job>ANALYST</job></employee> <employee><employee_number>7839</employee_number><employee_name>KING</employee_name><job>PRESIDENT</job></employee> <employee><employee_number>7844</employee_number><employee_name>TURNER</employee_name><job>SALESMAN</job></employee> <employee><employee_number>7876</employee_number><employee_name>ADAMS</employee_name><job>CLERK</job></employee> <employee><employee_number>7900</employee_number><employee_name>JAMES</employee_name><job>CLERK</job></employee> <employee><employee_number>7902</employee_number><employee_name>FORD</employee_name><job>ANALYST</job></employee> <employee><employee_number>7934</employee_number><employee_name>MILLER</employee_name><job>CLERK</job></employee> </employees>
Create a directory object to access the file.
conn / as sysdba alter session set container = pdb1; create or replace directory tmp_dir as '/tmp/'; grant read, write on directory tmp_dir to test; conn test/test@pdb1
External Table (XMLTAG) : Single Tag
Create an external table using the XMLTAG clause, which in this case indicates we want to return XML fragments with employee as a tag name.
drop table tab_ext;
create table tab_ext (
doc1 clob
)
organization external (
type oracle_loader
default directory tmp_dir
access parameters (
records
xmltag ("employee")
fields notrim
missing field values are null (
doc1 char(1000000)
)
)
location ('test1.xml')
)
reject limit unlimited;
We can see the XML fragments are being read correctly with the following query.
column doc1 format a120 select doc1 from tab_ext; DOC1 ------------------------------------------------------------------------------------------------------------------------ <employee><employee_number>7369</employee_number><employee_name>SMITH</employee_name><job>CLERK</job></employee> <employee><employee_number>7499</employee_number><employee_name>ALLEN</employee_name><job>SALESMAN</job></employee> <employee><employee_number>7521</employee_number><employee_name>WARD</employee_name><job>SALESMAN</job></employee> <employee><employee_number>7566</employee_number><employee_name>JONES</employee_name><job>MANAGER</job></employee> <employee><employee_number>7654</employee_number><employee_name>MARTIN</employee_name><job>SALESMAN</job></employee> <employee><employee_number>7698</employee_number><employee_name>BLAKE</employee_name><job>MANAGER</job></employee> <employee><employee_number>7782</employee_number><employee_name>CLARK</employee_name><job>MANAGER</job></employee> <employee><employee_number>7788</employee_number><employee_name>SCOTT</employee_name><job>ANALYST</job></employee> <employee><employee_number>7839</employee_number><employee_name>KING</employee_name><job>PRESIDENT</job></employee> <employee><employee_number>7844</employee_number><employee_name>TURNER</employee_name><job>SALESMAN</job></employee> <employee><employee_number>7876</employee_number><employee_name>ADAMS</employee_name><job>CLERK</job></employee> <employee><employee_number>7900</employee_number><employee_name>JAMES</employee_name><job>CLERK</job></employee> <employee><employee_number>7902</employee_number><employee_name>FORD</employee_name><job>ANALYST</job></employee> <employee><employee_number>7934</employee_number><employee_name>MILLER</employee_name><job>CLERK</job></employee> 14 rows selected. SQL>
We can present it as relational columns using the XMLTABLE operator.
select xt.*
from tab_ext x,
xmltable('/employee'
passing xmltype(x.doc1)
columns
"employee_number" number(4) path 'employee_number',
"employee_name" varchar2(10) path 'employee_name',
"job" varchar2(9) path 'job'
) xt
order by 1;
EMPLOYEE_NUMBER EMPLOYEE_N JOB
--------------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 rows selected.
SQL>
External Table (XMLTAG) : Multiple Tags
Create an external table using the XMLTAG clause, which indicates we want to return XML fragments with employee_number, employee_name or jobs as a tag name.
drop table tab_ext;
create table tab_ext (
doc1 varchar2(4000)
)
organization external (
type oracle_loader
default directory tmp_dir1
access parameters (
records
xmltag ("employee_number", "employee_name", "job")
fields notrim
missing field values are null
)
location ('test1.xml')
)
reject limit unlimited;
We can see the XML fragments are being read correctly with the following query.
column doc1 format a120 select doc1 from tab_ext; DOC1 ------------------------------------------------------------------------------------------------------------------------ <employee_number>7369</employee_number> <employee_name>SMITH</employee_name> <job>CLERK</job> <employee_number>7499</employee_number> <employee_name>ALLEN</employee_name> <job>SALESMAN</job> <employee_number>7521</employee_number> <employee_name>WARD</employee_name> <job>SALESMAN</job> <employee_number>7566</employee_number> <employee_name>JONES</employee_name> <job>MANAGER</job> <employee_number>7654</employee_number> <employee_name>MARTIN</employee_name> <job>SALESMAN</job> <employee_number>7698</employee_number> <employee_name>BLAKE</employee_name> <job>MANAGER</job> <employee_number>7782</employee_number> <employee_name>CLARK</employee_name> <job>MANAGER</job> <employee_number>7788</employee_number> <employee_name>SCOTT</employee_name> <job>ANALYST</job> <employee_number>7839</employee_number> <employee_name>KING</employee_name> <job>PRESIDENT</job> <employee_number>7844</employee_number> <employee_name>TURNER</employee_name> <job>SALESMAN</job> <employee_number>7876</employee_number> <employee_name>ADAMS</employee_name> <job>CLERK</job> <employee_number>7900</employee_number> <employee_name>JAMES</employee_name> <job>CLERK</job> <employee_number>7902</employee_number> <employee_name>FORD</employee_name> <job>ANALYST</job> <employee_number>7934</employee_number> <employee_name>MILLER</employee_name> <job>CLERK</job> 42 rows selected. SQL>
Since each fragment is treated separately it is presented as a separate row, and there is no relationship between the fragments.
For more information see:
Hope this helps. Regards Tim...