This article looks about the theme XMLTABLE around the following specifications : filtering rows with XPath, tag, attribute, nested table, variable with XML.
How XMLTABLE operator works in Oracle?
XMLTABLE operator create a map as result for given XQuery command, the map created shows the relation between rows and columns. The table will be recognized as the source of data.
The optional COLUMNS clause defines the columns of the virtual table to be created by XMLTable
Examples :
Consider a table EMPLOYEES which holds some XML data
CREATE TABLE EMPLOYEES ( id NUMBER, data XMLTYPE ) ;
Let’s insert a record in it, note that the XML contains employee related data for
- There are 4 employees in our xml file
- Each employee has a unique employee id
- Each employee also has an attribute position which defines whether an employee is admin or user
- Each employee has four child nodes: first name, last name, years on position and floor
INSERT INTO EMPLOYEES
VALUES (1,
xmltype ('<Employees>
<Employee id="001" position="admin">
<firstname>John</firstname>
<lastname>Watson</lastname>
<yearsonposition>10</yearsonposition>
<floor>white</floor>
</Employee>
<Employee id="002" position="admin">
<firstname>Sherlock</firstname>
<lastname>Homes</lastname>
<yearsonposition>3</yearsonposition>
<floor>white</floor>
</Employee>
<Employee id="003" position="user">
<firstname>Jim</firstname>
<lastname>Moriarty</lastname>
<yearsonposition>2</yearsonposition>
<floor>blue </floor>
</Employee>
<Employee id="004" position="user">
<firstname>Mycroft</firstname>
<lastname>Holmes</lastname>
<yearsonposition>4</yearsonposition>
<floor>red</floor>
</Employee>
</Employees>'
)
) ;
XPath Expressions
XPath expression allows to select nodes or list of nodes or attributes from a xml document.
| Expression | Description |
| Nodename | Selects all nodes with the name « nodename » |
| / | Selects from the root node |
| // | Selects nodes in the document from the current node that match the selection no matter where they are |
| . | Selects the current node |
| .. | Selects the parent of the current node |
| @ | Selects attributes |
| employee | Selects all nodes with the name “employee” |
| employees/employee | Selects all employee elements that are children of employees |
| //employee | Selects all employee elements no matter where they are in the document |
We can also use the predicates that are defined in square brackets [ … ]. They are used to find a specific node or a node that contains a specific value.
| Expression | Result |
| /employees/employee[1] | Selects the first employee element that is the child of the employee’s element. |
| /employees/employee[last()] | Selects nodes in the document from the current node that match the selection no matter where they are |
| /employees/employee[last()-1] | Selects the last but one employee element that is the child of the employees element |
| //employee[@position=’admin’] | Selects all the employee elements that have an attribute named position with a value of ‘admin’ |
Find out any others predicates : https://www.w3schools.com/xml/xpath_syntax.asp
Learning XMLTable operations
Below are few basic examples of using different expressions of XPath to fetch some information from xml document.
Notice this time the PATH expression uses a « @ » to indicate this is an attribute, rather than a tag .
Get first name and last name of all employees
SELECT t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x ;
Get employee type of all employees
SELECT emp.id, x.*
FROM employees emp,
XMLTABLE ('/Employees/Employee'
PASSING emp.data
COLUMNS
firstname VARCHAR2(30) PATH 'firstname',
position VARCHAR2(30) PATH '@position') x ;
Filtering rows with XPath
Get first name and last name of employee with id 002
SELECT t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee[@id=002]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x ;
Get first name and last name of employees who are admins
SELECT t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee[@position="admin"]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x ;
Get first name and last name of employees having yearsonposition > 3
SELECT t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee[yearsonposition>3]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname',
yearsonposition VARCHAR2(5) PATH 'yearsonposition') x ;
Get first name and last name of employees at last position
SELECT t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee[last()]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname',
yearsonposition VARCHAR2(5) PATH 'yearsonposition') x ;
Dynamic value for attribute
We could parameterise the year using variable in the XPath, which is prefixed with a « $ ». The value for the variable is then passed in the PASSING clause.
VARIABLE v_yearsonposition VARCHAR2(10);
EXEC :v_yearsonposition := '3';
SELECT t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee[yearsonposition>$yearsonposition]'
PASSING t.data, :v_yearsonposition AS "yearsonposition"
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname',
yearsonposition VARCHAR2(5) PATH 'yearsonposition') x ;
XMLTABLE operator and relational data
XMLTABLE operator, which allows you to project columns on to XML data in an XMLTYPE, making it possible to query the data directly from SQL as if it were relational data.
DROP TABLE DEPARTEMENT CASCADE CONSTRAINTS PURGE ; DROP TABLE EMPLOYEES CASCADE CONSTRAINTS PURGE ; CREATE TABLE DEPARTEMENT ( ID NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, NAME VARCHAR2(14), CITY VARCHAR2(13) ) ; CREATE TABLE EMPLOYEES ( ID NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, NAME VARCHAR2(10), JOB VARCHAR2(9), FEES NUMBER(7), DEPTNO NUMBER(2) CONSTRAINT FK_ID REFERENCES DEPARTEMENT ) ; INSERT INTO DEPARTEMENT VALUES (1,'ACCOUNTING','LONDON'); INSERT INTO DEPARTEMENT VALUES (2,'RESEARCH','PARIS'); INSERT INTO DEPARTEMENT VALUES (3,'SALES','MADRID'); INSERT INTO DEPARTEMENT VALUES (4,'OPERATIONS','NICE'); INSERT INTO EMPLOYEES VALUES (31,'ROSSI','CLERK',NULL,2); INSERT INTO EMPLOYEES VALUES (5345,'DUPONT','SALESMAN',300,3); INSERT INTO EMPLOYEES VALUES (57,'MARTIN','SALESMAN',500,3); INSERT INTO EMPLOYEES VALUES (98,'JOSEPH','MANAGER',NULL,2); INSERT INTO EMPLOYEES VALUES (43,'SCOTT','SALESMAN',1400,3); INSERT INTO EMPLOYEES VALUES (45,'GINO','MANAGER',NULL,3); INSERT INTO EMPLOYEES VALUES (46,'VALENTINO','MANAGER',NULL,1); INSERT INTO EMPLOYEES VALUES (47,'YANN','ANALYST',NULL,1); INSERT INTO EMPLOYEES VALUES (467,'BENOIT','PRESIDENT',NULL,1); INSERT INTO EMPLOYEES VALUES (17,'VALERIE','SALESMAN',0,3); INSERT INTO EMPLOYEES VALUES (53,'JAMES','CLERK',NULL,2); INSERT INTO EMPLOYEES VALUES (75,'JUNIOR','CLERK',NULL,3); INSERT INTO EMPLOYEES VALUES (6789,'FORD','ANALYST',NULL,2); INSERT INTO EMPLOYEES VALUES (4,'MILLER','CLERK',NULL,1);
Consider the following script; creating table which holds some XML data
DROP TABLE tablexml PURGE ;
CREATE TABLE tablexml (
id NUMBER,
xml_data XMLTYPE
) ;
DECLARE
r_EMPLOYEES XMLTYPE;
BEGIN
SELECT XMLELEMENT("employees",
XMLAGG(
XMLELEMENT("employee",
XMLFOREST(
e.id AS "empno",
e.name AS "ename",
e.job AS "job"
)
)
)
)
INTO r_EMPLOYEES
FROM EMPLOYEES e;
INSERT INTO tablexml VALUES (1, r_EMPLOYEES);
COMMIT;
END;
/
- XMLElement takes an element name for identifier
- The XMLElement function is typically nested to produce an XML document with a nested structure
- Using XMLFOREST gives us a separate tag for each column
- XMLAgg is an aggregate function. It takes a collection of XML fragments and returns an aggregated XML document
Query data
SELECT * FROM tablexml ;
or
SELECT t.xml_data.getClobVal() FROM tablexml t;
<employees>
<employee>
<empno>31</empno>
<ename>ROSSI</ename>
<job>CLERK</job>
</employee>
<employee>
<empno>5345</empno>
<ename>DUPONT</ename>
<job>SALESMAN</job>
</employee>
<employee>
<empno>57</empno>
<ename>MARTIN</ename>
<job>SALESMAN</job>
</employee>
….
</employees>
The XMLTABLE operator allows to split the XML data into rows and project columns on to it . The table column is identified as the source of the data using the PASSING clause . The rows are identified using a XQuery expression, in this case ‘/employees/employee’, columns are projected onto the resulting XML fragments using the COLUMNS clause, which identifies the relevant tags using the PATH expression.
SELECT xt.*
FROM tablexml x,
XMLTABLE('/employees/employee'
PASSING x.xml_data
COLUMNS
id VARCHAR2(4) PATH 'empno',
name VARCHAR2(10) PATH 'ename',
job VARCHAR2(9) PATH 'job'
) xt ;
Attribute-Based XML
This example uses attribute-based XML, where each data element for an employee is defined as an attribute of the employee tag, not a separate tag.
TRUNCATE TABLE tablexml
/
DECLARE
r_EMPLOYEES XMLTYPE;
BEGIN
SELECT XMLELEMENT("employees",
XMLAGG(
XMLELEMENT("employee",
XMLATTRIBUTES(
e.id AS "empno",
e.name AS "ename",
e.job AS "job"
)
)
)
)
INTO r_EMPLOYEES
FROM EMPLOYEES e;
INSERT INTO tablexml VALUES (1, r_EMPLOYEES);
COMMIT;
END;
/
- Using XMLATTRIBUTES creates an attribute for each column in the query.
Query data
SELECT t.xml_data.getClobVal() FROM tablexml t; <employees> <employee empno="31" ename="ROSSI" job="CLERK"/> <employee empno="5345" ename="DUPONT" job="SALESMAN"/> <employee empno="57" ename="MARTIN" job="SALESMAN"/> <employee empno="98" ename="JOSEPH" job="MANAGER"/> <employee empno="43" ename="SCOTT" job="SALESMAN"/> <employee empno="45" ename="GINO" job="MANAGER"/> <employee empno="46" ename="VALENTINO" job="MANAGER"/> <employee empno="47" ename="YANN" job="ANALYST"/> <employee empno="467" ename="BENOIT" job="PRESIDENT"/> …. </employees>
Nested XML
The simplest way to handle this to deal with the first layer, presenting the next layer down as an XML fragment in an XMLTYPE, which can then be processed using XMLTABLE in the next step.
The example below produces a list of departments, with every department containing a nested list of employees for that department.
TRUNCATE TABLE tablexml
/
DECLARE
r_EMPLOYEES XMLTYPE;
BEGIN
SELECT XMLELEMENT("departments",
XMLAGG(
XMLELEMENT("department",
XMLFOREST(
d.id AS "department_number",
d.name AS "department_name",
(SELECT XMLAGG(
XMLELEMENT("employee",
XMLFOREST(
e.id AS "employee_number",
e.name AS "employee_name"
)
)
)
FROM EMPLOYEES e
WHERE e.DEPTNO = d.id
) "employees"
)
)
)
)
INTO r_EMPLOYEES
FROM DEPARTEMENT d;
INSERT INTO tablexml VALUES (1, r_EMPLOYEES);
COMMIT;
END;
/
SELECT t.xml_data.getClobVal() FROM tablexml t;
<departments>
<department>
<department_number>1</department_number>
<department_name>ACCOUNTING</department_name>
<employees>
<employee>
<employee_number>46</employee_number>
<employee_name>VALENTINO</employee_name>
</employee>
<employee>
<employee_number>47</employee_number>
<employee_name>YANN</employee_name>
</employee>
.....
<employee>
<employee_number>75</employee_number>
<employee_name>JUNIOR</employee_name>
</employee>
</employees>
</department>
<department>
<department_number>4</department_number>
<department_name>OPERATIONS</department_name>
</department>
</departments>
The « departments_data » entry in the WITH clause extracts the basic department data, along with an XML fragment containing the employees for that department. The « employees_data » entry selects the department data from the « departments_data » entry, then extracts the employee information from the « employees » XMLTYPE using XMLTABLE in the normal way. Finally, we select the flattened data from the « employees_data » entry.
WITH
departments_data AS (
SELECT xt.*
FROM tablexml x,
XMLTABLE('/departments/department'
PASSING x.xml_data
COLUMNS
deptno VARCHAR2(4) PATH 'department_number',
dname VARCHAR2(10) PATH 'department_name',
employees XMLTYPE PATH 'employees'
) xt
),
employees_data AS (
SELECT deptno,
dname,
xt2.*
FROM departments_data dd,
XMLTABLE('/employees/employee'
PASSING dd.employees
COLUMNS
empno VARCHAR2(4) PATH 'employee_number',
ename VARCHAR2(10) PATH 'employee_name'
) xt2
)
SELECT * FROM employees_data ;
DEPT DNAME EMPN ENAME
---- ---------- ---- ----------
1 ACCOUNTING 46 VALENTINO
1 ACCOUNTING 47 YANN
1 ACCOUNTING 467 BENOIT
1 ACCOUNTING 4 MILLER
2 RESEARCH 31 ROSSI
2 RESEARCH 98 JOSEPH
2 RESEARCH 53 JAMES
2 RESEARCH 6789 FORD
3 SALES 5345 DUPONT
3 SALES 57 MARTIN
3 SALES 43 SCOTT
DEPT DNAME EMPN ENAME
---- ---------- ---- ----------
3 SALES 45 GINO
3 SALES 17 VALERIE
3 SALES 75 JUNIOR
That looks like it has worked, but we’ve lost department « 4 », which has no employees, for that using LEFT OUTER JOIN between the « departments_data » entry and the XMLTABLE
WITH
departments_data AS (
SELECT xt.*
FROM tablexml x,
XMLTABLE('/departments/department'
PASSING x.xml_data
COLUMNS
deptno VARCHAR2(4) PATH 'department_number',
dname VARCHAR2(10) PATH 'department_name',
employees XMLTYPE PATH 'employees'
) xt
),
employees_data AS (
SELECT deptno,
dname,
xt2.*
FROM departments_data dd
LEFT OUTER JOIN
XMLTABLE('/employees/employee'
PASSING dd.employees
COLUMNS
empno VARCHAR2(4) PATH 'employee_number',
ename VARCHAR2(10) PATH 'employee_name'
) xt2 ON 1=1
)
SELECT /*+ no_xml_query_rewrite */ * FROM employees_data ;
DEPT DNAME EMPN ENAME
---- ---------- ---- ----------
1 ACCOUNTING 46 VALENTINO
1 ACCOUNTING 47 YANN
1 ACCOUNTING 467 BENOIT
1 ACCOUNTING 4 MILLER
2 RESEARCH 31 ROSSI
2 RESEARCH 98 JOSEPH
2 RESEARCH 53 JAMES
2 RESEARCH 6789 FORD
3 SALES 5345 DUPONT
3 SALES 57 MARTIN
3 SALES 43 SCOTT
DEPT DNAME EMPN ENAME
---- ---------- ---- ----------
3 SALES 45 GINO
3 SALES 17 VALERIE
3 SALES 75 JUNIOR
4 OPERATIONS
- In addition, using the hint /*+ no_xml_query_rewrite */ to force the evaluation between tables
Find out about this workarounds : https://community.oracle.com/tech/apps-infra/discussion/4143859/outer-join-and-xmltable-does-not-always-work
We can do something similar for nested attribute-based XML .
The example below produces a list of departments, with every department containing a nested list of employees for that department. All the XML is attribute-based.
TRUNCATE TABLE tablexml;
DECLARE
r_EMPLOYEES XMLTYPE;
BEGIN
SELECT XMLELEMENT("departments",
XMLAGG(
XMLELEMENT("department",
XMLATTRIBUTES(
d.id AS "department_number",
d.name AS "department_name",
d.city AS "location"
),
XMLELEMENT("employees",
(SELECT XMLAGG(
XMLELEMENT("employee",
XMLATTRIBUTES(
e.id AS "employee_number",
e.name AS "employee_name"
)
)
)
FROM EMPLOYEES e
WHERE e.deptno = d.id
) "employees"
)
)
)
)
INTO r_EMPLOYEES
FROM DEPARTEMENT d;
INSERT INTO tablexml VALUES (1, r_EMPLOYEES);
COMMIT;
END;
/
Showing result
SELECT t.xml_data.getClobVal() FROM tablexml t;
<departments>
<department department_number="1" department_name="ACCOUNTING" location="LONDON">
<employees>
<employee employee_number="46" employee_name="VALENTINO"/>
<employee employee_number="47" employee_name="YANN"/>
<employee employee_number="467" employee_name="BENOIT"/>
<employee employee_number="4" employee_name="MILLER"/>
</employees>
</department>
<department department_number="2" department_name="RESEARCH" location="PARIS">
<employees>
<employee employee_number="31" employee_name="ROSSI"/>
<employee employee_number="98" employee_name="JOSEPH"/>
<employee employee_number="53" employee_name="JAMES"/>
<employee employee_number="6789" employee_name="FORD"/>
</employees>
</department>
....
<department department_number="4" department_name="OPERATIONS" location="NICE">
<employees/>
</department>
</departments>
We repeat the previous XMLTABLE query, but use « @ » for those elements that are attributes
WITH
departments_data AS (
SELECT xt.*
FROM tablexml x,
XMLTABLE('/departments/department'
PASSING x.xml_data
COLUMNS
deptno VARCHAR2(4) PATH '@department_number',
dname VARCHAR2(10) PATH '@department_name',
employees XMLTYPE PATH 'employees'
) xt
),
employees_data AS (
SELECT deptno,
dname,
xt2.*
FROM departments_data dd
LEFT OUTER JOIN
XMLTABLE('/employees/employee'
PASSING dd.employees
COLUMNS
empno VARCHAR2(4) PATH '@employee_number',
ename VARCHAR2(10) PATH '@employee_name'
) xt2 ON 1=1
)
SELECT /*+ no_xml_query_rewrite */ * FROM employees_data ;
DEPT DNAME EMPN ENAME
---- ---------- ---- ----------
1 ACCOUNTING 46 VALENTINO
1 ACCOUNTING 47 YANN
1 ACCOUNTING 467 BENOIT
1 ACCOUNTING 4 MILLER
2 RESEARCH 31 ROSSI
2 RESEARCH 98 JOSEPH
2 RESEARCH 53 JAMES
2 RESEARCH 6789 FORD
3 SALES 5345 DUPONT
3 SALES 57 MARTIN
3 SALES 43 SCOTT
DEPT DNAME EMPN ENAME
---- ---------- ---- ----------
3 SALES 45 GINO
3 SALES 17 VALERIE
3 SALES 75 JUNIOR
4 OPERATIONS