XMLTABLE

Purpose

This function is used to map the results returned by XPath to rows and columns of a relational table. You can use the results returned by SQL queries as virtual relational tables.

The key capabilities of the XMLTABLE function include:

• Data extraction: By specifying appropriate XPath expressions in XMLTABLE, you can precisely extract the required data from complex XML documents.

• Data structuring: XMLTABLE can map specific parts of XML data to rows and columns of relational tables, converting semi-structured XML data into structured table form, making it easier to perform SQL queries and analysis.

• Table conversion: You can use the structured data generated by XMLTABLE to create virtual tables and use these table data to generate new tables or views, allowing you to save or further process the data 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 specified for the columns in the XMLTable function.

Examples

Use the XMLTABLE function to convert XML data into an SQL table containing employee IDs, names, positions, and salaries. Here's how:

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

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

3. Use the PASSING clause to specify the XML document to be passed to the XQuery expression. In this example, the entire XML document is encapsulated using the XMLTYPE constructor. XMLTYPE converts the XML data from string format into an XML type that the database can process.

4. In the COLUMNS clause, define four columns to map the extracted data from the XML. The mapping is as follows:

   • The employee_id column maps to the emp_id under each employee element.
   • The employee_name column maps to the name under each employee element.
   • The position column maps to the position under each employee element.
   • The salary column maps to the salary under each employee element.

   The PATH clause after each column defines where the corresponding values are extracted.

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 result is as follows:

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