- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to extract XML data for relational use?
Problem Statement:
You need to extract individual elements values from an XML document.
Solution:
Oracle provides the XMLTABLE function to manipulate XML documents using XQuery and column mapping to Oracle datatypes. Using XMLTABLE, we can identify and use data elements in an XML document in a relational way.
Let us assume, below XML document is stored in a table tmp_xml_gen from which we wanted to extract the elements. We have customer details along with order related information.
Example
<?xml version="1.0"?> <customers> <customer> <customer_id>134</customer_id> <email>taylor.cauchon@internalmail</email> <name>Taylor Cauchon</name> <orders> <order> <order_id>921</order_id> <status>COMPLETE</status> </order> <order> <order_id>485</order_id> <status>COMPLETE</status> </order> <order> <order_id>1528</order_id> <status>COMPLETE</status> </order> <order> <order_id>1672</order_id> <status>COMPLETE</status> </order> <order> <order_id>1319</order_id> <status>COMPLETE</status> </order> </orders> </customer> </customers>
Code
WITH cust AS -- Customer details (SELECT xt.customer_id, xt.email, xt.name, x.result FROM tmp_xml_gen x, XMLTABLE('/customers/customer' PASSING x.RESULT COLUMNS customer_id VARCHAR2(10) PATH 'customer_id', email VARCHAR2(255) PATH 'email', name VARCHAR2(255) PATH 'name') xt), -- Order details ord AS (SELECT customer_id, email, name, xt2.* FROM cust t, XMLTABLE('//orders/order' PASSING t.result COLUMNS order_id VARCHAR2(4) PATH 'order_id', status VARCHAR2(10) PATH 'status' ) xt2) SELECT * FROM ord;
Output: Few rows from above SQL
134 taylor.cauchon@internalmail Taylor Cauchon 921 COMPLETE 134 taylor.cauchon@internalmail Taylor Cauchon 485 COMPLETE 134 taylor.cauchon@internalmail Taylor Cauchon 1528 COMPLETE 134 taylor.cauchon@internalmail Taylor Cauchon 1672 COMPLETE 134 taylor.cauchon@internalmail Taylor Cauchon 1319 COMPLETE 135 marc.domanski@internalmail Marc Domanski 50 COMPLETE 135 marc.domanski@internalmail Marc Domanski 99 COMPLETE 135 marc.domanski@internalmail Marc Domanski 142 COMPLETE 135 marc.domanski@internalmail Marc Domanski 195 COMPLETE
The XMLTABLE function takes one or more XQuery expressions to apply to a column or expression of XMLTYPE data. The general form of XMLTABLE is:
xmltable( <XQuery to apply>, <Source XMLTYPE column or expression>, <Column definition optionally mapped with XQuery path expression>).
- Related Articles
- How to extract Key XML Elements from an XML Document in Oracle?
- Relational Data Model
- How to use XMLPullParser to parse XML in Android?
- How to translate SQL data to XML in Oracle?
- Object-relational Data Model
- How to Extract Wikipedia Data in Python?
- How to store XML data in a table in Oracle?
- How to use XMLPullParser to parse XML in Android using Kotlin?
- What is Multi-relational Data Mining?
- How to extract raw form data in Laravel?
- How to extract data from a Matplotlib plot?
- How to store data temporarily for later use in Oracle?
- How to extract data from SAP using .NET provider.
- How to use TestNG data providers for parameterization in Rest Assured?
- How to extract required data from structured strings in Python?
