This example uses tag-based XML, where each data element for an employee is surrounded by its own start and end tag.
First we create a table to hold our XML document and populate it with a document containing multiple rows of data. Using XMLFOREST gives us a separate tag for each column in the query.
CREATETABLE xml_tab ( id NUMBER, xml_data XMLTYPE );
DECLARE l_xmltype XMLTYPE; BEGIN SELECT XMLELEMENT("employees", XMLAGG( XMLELEMENT("employee", XMLFOREST( e.empno AS "empno", e.ename AS "ename", e.job AS "job", TO_CHAR(e.hiredate, 'DD-MON-YYYY') AS "hiredate" ) ) ) ) INTO l_xmltype FROM emp e;
The XMLTABLE operator allows us to split the XML data into rows and project columns on to it. We effectively make a cartesian product between the data table and the XMLTABLE call, which allows XMLTABLE to split a XML document in a single row into multiple rows in the final result set. 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 and assigns the desired column names and data types. Be careful with the names of the columns in the COLUMNS clause. If you use anything other than upper case, they will need to be quoted to make direct reference to them. Notice we are querying using the alias of the XMLTABLE call, rather than the regular table alias.
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 the table we defined for the previous example and populate it with a document containing multiple rows of data. Using XMLATTRIBUTES creates an attribute for each column in the query.
DECLARE l_xmltype XMLTYPE; BEGIN SELECT XMLELEMENT("employees", XMLAGG( XMLELEMENT("employee", XMLATTRIBUTES( e.empno AS "empno", e.ename AS "ename", e.job AS "job", TO_CHAR(e.hiredate, 'DD-MON-YYYY') AS "hiredate" ) ) ) ) INTO l_xmltype FROM emp e;
The XMLTABLE operator allows us to split the XML data into rows and project columns on to it. Notice this time the PATH expression uses a “@” to indicate this is an attribute, rather than a tag.
So far we have dealt with simple XML, but we sometimes have to deal with XML containing multiple levels of nesting. 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.
Truncate the test table and insert a row of nested XML. The example below produces a list of departments, with every department containing a nested list of employees for that department.
DECLARE l_xmltype XMLTYPE; BEGIN SELECT XMLELEMENT("departments", XMLAGG( XMLELEMENT("department", XMLFOREST( d.deptno AS "department_number", d.dname AS "department_name", (SELECT XMLAGG( XMLELEMENT("employee", XMLFOREST( e.empno AS "employee_number", e.ename AS "employee_name" ) ) ) FROM emp e WHERE e.deptno = d.deptno ) "employees" ) ) ) ) INTO l_xmltype FROM dept d;
To make things simpler we’ve split out the layers using the WITH clause, but this could also be done with inline-views. 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.
DEPT DNAME EMPN ENAME ---- ---------- ---- ---------- 10 ACCOUNTING 7782 CLARK 10 ACCOUNTING 7839 KING 10 ACCOUNTING 7934 MILLER 20 RESEARCH 7369 SMITH 20 RESEARCH 7566 JONES 20 RESEARCH 7788 SCOTT 20 RESEARCH 7876 ADAMS 20 RESEARCH 7902 FORD 30 SALES 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7844 TURNER 30 SALES 7900 JAMES
14rows selected.
SQL>
That looks like it has worked, but we’ve lost department “40”, which has no employees. If we want to show that row we need to do a LEFT OUTER JOIN between the “departments_data” entry and the XMLTABLE , as shown below. Notice the join condition of “1=1” in the second WITH clause entry.
DEPT DNAME EMPN ENAME ---- ---------- ---- ---------- 10 ACCOUNTING 7782 CLARK 10 ACCOUNTING 7839 KING 10 ACCOUNTING 7934 MILLER 20 RESEARCH 7369 SMITH 20 RESEARCH 7566 JONES 20 RESEARCH 7788 SCOTT 20 RESEARCH 7876 ADAMS 20 RESEARCH 7902 FORD 30 SALES 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7844 TURNER 30 SALES 7900 JAMES 40 OPERATIONS
Not all XML data you want to process is already stored in a table. In some cases, the XML is stored in a PL/SQL variable. The XMLTABLE operator can work with this also.
In the previous example, the XML was being held in regular string variable, so we we had to convert it to an XMLTYPE using a constructor in the PASSING clause. If the data had already been in an XMLTYPE variable, this constructor would not have been necessary. Let’s repeat the previous example, but put the XML into an XMLTYPE before using XMLTABLE on it. Notice the PASSING clause no longer needs the XMLTYPE constructor.
FOR cur_rec IN ( SELECT a.mydata, xt.* FROM ( -- Pull out just the CDATA value. SELECT EXTRACTVALUE(XMLTYPE(l_xml), '//sawsoap:rowset/text()','xmlns:sawsoap="urn://oracle.bi.webservices/v6"') AS mydata FROM dual ) a, -- Specify the path that marks a new row, remembering to use the correct namespace. XMLTABLE(XMLNAMESPACES(default'urn:schemas-microsoft-com:xml-analysis:rowset'), '/rowset/Row' PASSING XMLTYPE(a.mydata) COLUMNS column0 NUMBER(4) PATH 'Column0', column1 VARCHAR2(20) PATH 'Column1' ) xt) LOOP DBMS_OUTPUT.put_line('column0='|| cur_rec.column0 ||' column1='|| cur_rec.column1); END LOOP; END; / column0=1000 column1=East Region column0=2000 column1=West Region column0=1500 column1=Central Region
EMPN ENAME JOB HIREDATE ---- ---------- --------- ----------- 7369 SMITH CLERK 17-DEC-1980 7876 ADAMS CLERK 23-MAY-1987 7900 JAMES CLERK 03-DEC-1981 7934 MILLER CLERK 23-JAN-1982
4rows selected.
SQL>
We could parameterise the job type using variable in the XPath, which is prefixed with a “$”. The value for this variable is then passed in the PASSING clause. The variable must be aliases using AS and double quoted to make sure the name and case matches that of the variable in the XPath expression.
The XMLTABLE operator works really well with small XML documents, or tables with many rows, each of which contain a small XML document. As the XML documents get bigger the performance gets worse compared to the manual parse method. When dealing with large XML documents you may have to forgo the convenience for the XMLTABLE operator in favour of a manual solution.
