Here’s a prototype for using the SQL/XML function XMLTABLE to map XML data into relational rows and columns.
This solution uses the standard EMP table — the same can be extended to work with any XMLTYPE-relational mapping.
Problem Statement: Receive XML Payload, Parse and Store in Relational Table
An application receives employee data in XML format. The XML payload contains details of a list of employees associated with a department and hire date. For example, the payload for department 10 and hire date 2016-APR-02, with two employees, looks like this:
<data>
<deptNo>10</deptNo>
<hireDate>2016-APR-02</hireDate>
<!--List of employees with common deptNo and hireDate-->
<empList>
<emp>
<empNo>98</empNo>
<eName>KINGSLEY SHACKLEBOLT</eName>
<job>AGENT</job>
<salary>50000</salary>
</emp>
<emp>
<empNo>99</empNo>
<eName>FILIUS FLITWICK</eName>
<job>PROFESSOR</job>
<salary>44000</salary>
</emp>
</empList>
</data>
An external system inserts such XML payloads into an XMLTYPE column in staging table EMP_PAYLOAD, with status PENDING.
Table EMP_PAYLOAD has this structure:
create table emp_payload ( messageid NUMBER , status VARCHAR2(10) , empxml XMLTYPE );
A scheduled program needs to pick records with status PENDING from EMP_PAYLOAD, map them to EMP table, and:
- INSERT into EMP table if the records are new
- UPDATE the records in EMP table if the records exist already
Once done, the PENDING status in EMP_PAYLOAD is to be changed to PROCESSED.
Solution Summary
This requirement can be met by:
- using XMLTABLE to parse the XML data into relational rows and columns
- wrapping a MERGE statement around the relational representation of the XML data to update/insert into the target table
- post-MERGE, issuing a simple UPDATE on the source table to change status from PENDING to PROCESSED
PL/SQL script solution with XMLTABLE
A PL/SQL script to achieve the required result. A description of XMLTABLE and run result follows.
begin
merge into emp a
using
(select empno
, ename
, job
, hiredate
, sal
, deptno
from emp_payload m
, xmltable('//data//emp' passing m.empxml
columns
empno NUMBER(4) PATH 'empNo'
, ename VARCHAR2(30) PATH 'eName'
, job VARCHAR2(9) PATH 'job'
, hiredate DATE PATH './../../hireDate'
, sal NUMBER(7,2) PATH 'salary'
, deptno NUMBER(2) PATH './../../deptNo'
) t
where status = 'PENDING') b
on (a.empno = b.empno)
when matched then
update set
a.ename = b.ename
, a.job = b.job
, a.sal = b.sal
, a.deptno = b.deptno
when not matched then
insert
(empno
, ename
, job
, hiredate
, sal
, deptno)
values
(b.empno
, b.ename
, b.job
, b.hiredate
, b.sal
, b.deptno);
update emp_payload
set status = 'PROCESSED' where status = 'PENDING';
end;
/
[Implemented in Oracle 12.1]
Understanding the SQL/XML function XMLTABLE
The SQL/XML function XMLTABLE is used in the FROM clause of a SQL, in conjunction with a driving table that holds XML data, to convert the XML data to relational form. Interestingly, XMLTABLE joins implicitly with its driving table; no join condition needs to be spelled out for it.
- The PASSING clause specifies the XMLTYPE data to be processed by the XMLTABLE function.
- The COLUMNS clause projects the XML data into relational form. Each entry in the COLUMNS clause defines a virtual table column, assigning it a column name and data type.
- The PATH expression uses XQuery to maps the columns to XML elements in the payload.
PL/SQL script solution with XMLTABLE: Run results
Data in EMP table before merging the XML payload:
SQL> SELECT empno 2 , ename 3 , job 4 , hiredate 5 , sal 6 , deptno 7 FROM emp; EMPNO ENAME JOB HIREDATE SAL DEPTNO ----- --------------------- --------- --------- ------ ---------- 7788 SCOTT ANALYST 19-APR-87 3000 20 7876 ADAMS CLERK 23-MAY-87 1100 20 98 KINGSLEY SHACKLEBOLT AUROR 02-APR-16 45000 10
XML payload with PENDING status:
SQL> select * from emp_payload;
MESSAGEID STATUS EMPXML
--------- ------- ------------------------------------------
1 PENDING <data>
<deptNo>20</deptNo>
<hireDate>2017-AUG-13</hireDate>
<!--List of employees with common deptNo
and hireDate-->
<empList>
<emp>
<empNo>97</empNo>
<eName>BATHILDA BAGSHOT</eName>
<job>HISTORIAN</job>
<salary>55000</salary>
</emp>
</empList>
</data>
2 PENDING <data>
<deptNo>10</deptNo>
<hireDate>2016-APR-02</hireDate>
<!--List of employees with common deptNo
and hireDate-->
<empList>
<emp>
<empNo>98</empNo>
<eName>KINGSLEY SHACKLEBOLT</eName>
<job>AGENT</job>
<salary>50000</salary>
</emp>
<emp>
<empNo>99</empNo>
<eName>FILIUS FLITWICK</eName>
<job>PROFESSOR</job>
<salary>44000</salary>
</emp>
</empList>
</data>
We see two rows in the EMP_PAYLOAD table, containing three employee records to be processed overall.
After merging the XML payload:
– Two new rows (empnos 97 and 99) should be inserted
– One row (empno 98) should be updated
Script run:
SQL> begin
2 merge into emp a
3 using
4 (select empno
5 , ename
6 , job
7 , hiredate
8 , sal
9 , deptno
10 from emp_payload m
11 , xmltable('//data//emp' passing m.empxml
12 columns
13 empno NUMBER(4) PATH 'empNo'
14 , ename VARCHAR2(30) PATH 'eName'
15 , job VARCHAR2(9) PATH 'job'
16 , hiredate DATE PATH './../../hireDate'
17 , sal NUMBER(7,2) PATH 'salary'
18 , deptno NUMBER(2) PATH './../../deptNo'
19 ) t
20 where status = 'PENDING') b
21 on (a.empno = b.empno)
22 when matched then
23 update set
24 a.ename = b.ename
25 , a.job = b.job
26 , a.sal = b.sal
27 , a.deptno = b.deptno
28 when not matched then
29 insert
30 (empno
31 , ename
32 , job
33 , hiredate
34 , sal
35 , deptno)
36 values
37 (b.empno
38 , b.ename
39 , b.job
40 , b.hiredate
41 , b.sal
42 , b.deptno);
43
44 update emp_payload
45 set status = 'PROCESSED' where status = 'PENDING';
46
47 end;
48 /
PL/SQL procedure successfully completed.
Data in EMP table after merging the XML payload:
SQL> SELECT empno 2 , ename 3 , job 4 , hiredate 5 , sal 6 , deptno 7 FROM emp; EMPNO ENAME JOB HIREDATE SAL DEPTNO ----- --------------------- --------- --------- ------ ---------- 7788 SCOTT ANALYST 19-APR-87 3000 20 7876 ADAMS CLERK 23-MAY-87 1100 20 98 KINGSLEY SHACKLEBOLT AGENT 02-APR-16 50000 10 97 BATHILDA BAGSHOT HISTORIAN 13-AUG-17 55000 20 99 FILIUS FLITWICK PROFESSOR 02-APR-16 44000 10
After running the PL/SQL script using XMLTABLE, we do see the result in EMP table as expected.
– Two new rows (empnos 97 and 99) have been inserted, taking the record count up from 3 to 5
– For empno 98, the job and salary have been updated
Summary
This article describes the SQL/XML function XMLTABLE, with a working prototype for interpreting XML data and converting it to relational form.
