In Oracle, you can convert XML data into a table-like
structure using XMLTABLE or EXTRACT functions. These functions
allow you to parse XML and extract specific elements or attributes into rows
and columns.
1. Using XMLTABLE
XMLTABLE is a powerful function that lets you query and
project XML data into relational rows and columns.
Example XML Data:
Let's say we have the following XML stored in a table:
CREATE TABLE xml_data (id NUMBER, xml_content CLOB);
INSERT INTO xml_data VALUES (1, '<employees>
<employee>
<id>101</id>
<name>John Doe</name>
<department>Sales</department>
</employee>
<employee>
<id>102</id>
<name>Jane Smith</name>
<department>Marketing</department>
</employee>
</employees>');
Query Using XMLTABLE:
SELECT x.id AS employee_id,
x.name AS
employee_name,
x.department AS
employee_department
FROM xml_data,
XMLTABLE('/employees/employee'
PASSING
XMLTYPE(xml_content)
COLUMNS
id
NUMBER PATH 'id',
name
VARCHAR2(100) PATH 'name',
department VARCHAR2(100) PATH 'department') x;
Output:
|
EMPLOYEE_ID |
EMPLOYEE_NAME |
EMPLOYEE_DEPARTMENT |
|
101 |
John Doe |
Sales |
|
102 |
Jane Smith |
Marketing |
2. Using EXTRACT and EXTRACTVALUE (Deprecated)
The EXTRACT and EXTRACTVALUE functions can extract values
from XML, but these are less efficient and have been deprecated in favor of XMLTABLE.
Query Example:
SELECT EXTRACTVALUE(VALUE(e), '/employee/id') AS
employee_id,
EXTRACTVALUE(VALUE(e),
'/employee/name') AS employee_name,
EXTRACTVALUE(VALUE(e),
'/employee/department') AS employee_department
FROM xml_data d,
TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(d.xml_content),
'/employees/employee'))) e;
3. Using XMLSEQUENCE
If you want to treat each XML node as a row, you can use XMLSEQUENCE
with EXTRACT.
Example:
SELECT x.*
FROM xml_data,
TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(xml_content),
'/employees/employee'))) x;
4. Example with Inline XML
If the XML is not stored in a table but provided directly,
you can use XMLTABLE with XMLTYPE.
Query Example:
SELECT x.id AS employee_id,
x.name AS
employee_name,
x.department AS
employee_department
FROM XMLTABLE('/employees/employee'
PASSING
XMLTYPE('<employees>
<employee>
<id>101</id>
<name>John Doe</name>
<department>Sales</department>
</employee>
<employee>
<id>102</id>
<name>Jane Smith</name>
<department>Marketing</department>
</employee>
</employees>')
COLUMNS
id
NUMBER PATH 'id',
name
VARCHAR2(100) PATH 'name',
department VARCHAR2(100) PATH 'department') x;
5. Key Points
- XPath Syntax: XPath is used to navigate the XML hierarchy. For example:
- /employees/employee: Selects all <employee> nodes.
- id: Selects the <id> element inside each <employee> node.
- Data Types in XMLTABLE: Specify column data types to match the XML content.
- Performance Tip: Use XMLTABLE for better performance with large XML data compared to EXTRACT.