XMLTABLE

Purpose

XMLTABLE() maps the result returned based on an XPath into relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL.

XMLTABLE() provides the following key features:

• Data extraction: You can specify an XPath expression in XMLTABLE() to accurately extract the desired parts of data from a complex XML document.

• Data structuring: XMLTABLE() can map specific parts of XML data to relational rows and columns and thereby convert semi-structured XML data into structured tabular form, making SQL queries and analysis easier.

• Table conversion: You can use structured data generated by XMLTABLE() to create virtual tables and use these table data to generate new tables or views for further processing in the database.

Syntax

XMLTABLE( [ XMLnamespaces_clause , ] XQuery_string XMLTABLE_options )

XMLnamespaces_clause:
 XMLNAMESPACES( { string AS identifier } | { DEFAULT string } [, { string AS identifier } | { DEFAULT string } ]...)

XMLTABLE_options:
  [ XML_passing_clause ]
  [ COLUMNS XML_table_column [, XML_table_column]...]

XML_passing_clause:
  PASSING [ BY VALUE ] expr [ AS identifier ]

XML_table_column:
  column { FOR ORDINALITY
          | { datatype | XMLTYPE } [ PATH string ] [ DEFAULT expr ]
  }

Parameters

Return type

The return type depends on the data type of the column specified in the XMLTable() function.

Examples

Call XMLTABLE() to convert XML data into an SQL table with columns of the employee ID, name, position, and salary. The details are as follows:

1. Define the default namespace http://www.example.com/employees to match the namespace in the XML data.

2. Define the XQuery expression '/employees/employee' to select all employee elements.

3. Specify the PASSING clause to pass the XML document to the XQuery expression. This example uses the XMLTYPE() constructor to encapsulate the entire XML document. XMLTYPE() converts XML data in the string form into XMLType data that the database can process.

4. In the COLUMNS clause, define four columns that hold the data extracted from the XML document. The mappings are as follows:

   • The employee_id column is mapped to emp_id under each employee element.
   • The employee_name column is mapped to name under each employee element.
   • The position column is mapped to position under each employee element.
   • The salary column is mapped to salary under each employee element.

   The PATH clause after each column defines where to extract the corresponding values.

SELECT *
FROM XMLTABLE(
       XMLNAMESPACES(DEFAULT 'http://www.example.com/employees'),
       '/employees/employee'
       PASSING XMLTYPE('
          <employees xmlns="http://www.example.com/employees">
               <employee>
                    <emp_id>1</emp_id>
                    <name>John Doe</name>
                    <position>Developer</position>
                    <salary>5000</salary>
               </employee>
               <employee>
                    <emp_id>2</emp_id>
                    <name>Jane Smith</name>
                    <position>Manager</position>
                    <salary>6500</salary>
               </employee>
          </employees>'
     )
     COLUMNS
          employee_id    INT           PATH 'emp_id',
          employee_name  VARCHAR2(100) PATH 'name',
          position       VARCHAR2(50)  PATH 'position',
          salary         NUMBER        PATH 'salary'
) AS emp_tab;

The return result is as follows:

+-------------+---------------+-----------+--------+
| EMPLOYEE_ID | EMPLOYEE_NAME | POSITION  | SALARY |
+-------------+---------------+-----------+--------+
|           1 | John Doe      | Developer |   5000 |
|           2 | Jane Smith    | Manager   |   6500 |
+-------------+---------------+-----------+--------+
2 rows in set