Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Jai Shree Ram Oracle
  • Changing Instance Name ( No DB_NAME) Oracle
  • This is im telling Kishore Oracle
  • How to change hostname in Linux Linux/Unix
  • My FTP Job Scheduling for www.pvmehta.com PHP/MYSQL/Wordpress
  • oracle 11g RAC on vmware Oracle
  • DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS Oracle
  • Updated LCK.SQL file. Oracle
  • create database syntax Oracle
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • Search and replace editor command in vi Linux/Unix
  • dbms_job.submit example Oracle
  • Adding addidional hard drive and attach it to a linux box. Linux/Unix
  • To see how much time or progress of long transaction Oracle
  • My Minimum Tuning Programs Oracle

JSON/XML Types in Oracle

Posted on 18-Mar-202518-Mar-2025 By Admin No Comments on JSON/XML Types in Oracle

Handling JSON and XML Data in Oracle

Oracle provides built-in support for JSON and XML data storage, querying, and manipulation. Below are methods to handle both data types efficiently.


1. Handling JSON Data in Oracle

A. Storing JSON Data

  • JSON can be stored in:
    • CLOB (Character Large Object)
    • VARCHAR2 (for small JSON documents)
    • BLOB
    • Native JSON Data Type (from Oracle 21c)

Example: Creating a Table with JSON Column

CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    details CLOB CHECK (details IS JSON) -- Enforce JSON format
);

B. Inserting JSON Data

INSERT INTO employees VALUES (1, 'John Doe', '{"age": 30, "role": "Developer"}');

C. Querying JSON Data

1. Extracting JSON Values Using JSON_VALUE

SELECT name, JSON_VALUE(details, '$.age') AS age
FROM employees
WHERE JSON_VALUE(details, '$.role') = 'Developer';
  • $.age → Extracts age field.
  • $.role → Filters JSON objects where role = Developer.

2. Querying JSON Data Using JSON_TABLE

SELECT emp_id, jt.age, jt.role
FROM employees,
     JSON_TABLE(details, '$' COLUMNS (age NUMBER PATH '$.age', role VARCHAR2(100) PATH '$.role')) jt;
  • Converts JSON fields into relational table format.

D. Updating JSON Data

UPDATE employees 
SET details = JSON_TRANSFORM(details, SET '$.age' = 35)
WHERE id = 1;
  • JSON_TRANSFORM modifies JSON fields without rewriting the entire JSON document.

E. Indexing JSON Data

For faster retrieval, create a JSON Search Index:

CREATE INDEX emp_json_idx ON employees (details) INDEXTYPE IS JSON;

2. Handling XML Data in Oracle

A. Storing XML Data

XML can be stored in:

  • CLOB
  • XMLType (Oracle’s optimized XML storage type)

Example: Creating a Table with XML Column

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_details XMLTYPE
);

B. Inserting XML Data

INSERT INTO orders VALUES (101, XMLTYPE('<order><customer>John</customer><total>100</total></order>'));

C. Querying XML Data

1. Extracting XML Data Using EXTRACTVALUE

SELECT order_id, EXTRACTVALUE(order_details, '/order/customer') AS customer
FROM orders;
  • Retrieves customer name from the XML structure.

2. Using XMLTABLE for More Complex Queries

SELECT o.order_id, x.customer, x.total
FROM orders o,
     XMLTABLE('/order' PASSING o.order_details
              COLUMNS customer VARCHAR2(100) PATH 'customer',
                      total NUMBER PATH 'total') x;
  • Converts XML fields into relational table format.

D. Updating XML Data

Using XMLQuery and XMLModify:

UPDATE orders
SET order_details = XMLModify(order_details, 'replace value of (/order/total/text())[1] with "150"')
WHERE order_id = 101;
  • Updates the total amount in the XML.

E. Indexing XML Data

For fast lookups, create an XML Index:

CREATE INDEX order_xml_idx ON orders (order_details) INDEXTYPE IS XDB.XMLINDEX;

3. JSON vs. XML in Oracle

FeatureJSONXML
StorageCLOB, VARCHAR2, BLOB, JSON TypeXMLType, CLOB
Query LanguageSQL with JSON functionsXPath, XQuery
PerformanceFaster for small, simple documentsBetter for hierarchical, large data
IndexingJSON Index (JSON_VALUE, JSON_TABLE)XML Index (XMLIndex)

Conclusion

  • Use JSON for lightweight, structured data (API responses, logging).
  • Use XML when hierarchical structure and schema validation are needed.
  • Indexes improve performance for querying both JSON and XML.
  • Conversion Functions (JSON_TABLE, XMLTABLE) help integrate them with relational queries.

Oracle

Post navigation

Previous Post: CPU Core related projections
Next Post: How to connect to Oracle Database with Wallet with Python.

Related Posts

  • All About Trace Fils Oracle
  • Oracle10g – Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository Oracle
  • Exadata Basics Oracle
  • Absolute file number and relative file number Oracle
  • Configure ssh authentications for RAC Oracle
  • Rman Notes -1 Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Giving Grant on v$DATABASE Oracle
  • sess_server.sql Oracle
  • Disbaling DBA_SCHEDULER_JOBS Oracle
  • Error Handling in Proc Oracle
  • FRA Information. Oracle
  • create user with unlimited quota Oracle
  • My Test Case On 21-OCT-2005 Oracle
  • sid_wise_cursor.sql find open cursor basis on username or SID Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme