XMLTABLE

Purpose

The XMLTABLE function is used to map the results returned by an XPath expression to rows and columns in a relational table. You can use SQL queries to retrieve the results of the XMLTABLE function as a virtual relational table.

The key capabilities of the XMLTABLE function include:

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

• Data structuring: The XMLTABLE function can map specific parts of XML data to rows and columns in a relational table, converting semi-structured XML data into a structured table format, making it easier to query and analyze with SQL.

• Table conversion: You can use the structured data generated by the XMLTABLE function to create virtual tables. These tables can then be used to generate new tables or views, allowing you to store or further process the data within 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 types 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. The steps are as follows:

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 (employee) elements.

3. Use the PASSING clause to specify the XML document 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 mappings are 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 the source of the corresponding value.

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