Oracle: Convert XML to JSON
NickName:Priyanka Mokashi Ask DateTime:2018-09-28T02:27:01

Oracle: Convert XML to JSON

I am writing one stored procedure where by using db link I am copying data of one table and inserting it into my another db. But before inserting I need to convert one clob field which is in xml to json.

I know by using APEX package we have json functionality but it is available from version 5.0.

My Apex version is 4.2.5.00.08.

Is there any way to covert XML field into json in oracle without using apex package?

Edit : I have already tried link

It is not woking in my case. Please help with some alternative way.

Edit 2: As suggested my oracle version is below 12.2. So I tried using Oracle Create Java Statement. I want to import some json package in create java statement. Any idea how to import java json packages to use it in create java statement in oracle?

Edit 3: I am able to do convert xml to json using load java utility. For this Followed below steps:

  1. Create java program to convert xml to json and create jar of it
  2. Load your JAR using

'call dbms_java.loadjava('PATH OF JAR FILE')'

  1. Create pl/sql function like below :

CREATE OR REPLACE FUNCTION getXmltoJsonData(xmlString in VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'CreatedJavaClass.createdMethod(java.lang.String) return java.lang.String'; /

  1. Now call this function to get json string.

select getXmltoJsonData (xmlString) as result from dual;

Copyright Notice:Content Author:「Priyanka Mokashi」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/52543092/oracle-convert-xml-to-json

More about “Oracle: Convert XML to JSON” related questions

Oracle: Convert XML to JSON

I am writing one stored procedure where by using db link I am copying data of one table and inserting it into my another db. But before inserting I need to convert one clob field which is in xml t...

Show Detail

How to convert JSON into XML in Oracle 10g

I work on Oracle 10g where it's possible to use the built-in functions for handling XML documents but not JSON datatype bacause as far as I know that is only possible from Oracle 12c on. Aside from

Show Detail

Oracle with XML to Postgres in JSON Converstion

I am trying to migrate Oracle codes to Postgres, at the same time in Oracle we are using XML and in PostgreSQL we are using JSON. So while migrating I am stuck with few basic points, like in oracl...

Show Detail

How to convert RECORD type parameter to XML or json in oracle?

R_Al.op_record is a user defined record type in oracle. I can not compile the following code. What I want is to convert the user defined RECORD type in to an xml or json without referring to its

Show Detail

XML to JSON conversion [oracle.xdb.XMLType to JSON object] performance issue

The application model is UI<->JavaServerside<->Oracle StoredProcedures[DB] I retrieve the XML data received from the Stored procedure XML-Out and pass it to the UI as a JSON object. Here's ...

Show Detail

convert oracle blob to xml type

I have experience using MSSQL 2008 and I recently had to move from MSSQL to Oracle 10g. The people who designed the (Oracle) table, which has a column I need to extract data from, used a BLOB type ...

Show Detail

Is it possible to ouput my data in JSON instead of XML in Oracle 11g

I currently have an API built with PL/SQL that uses Oracle 11g. It currently outputs the data in XML. I have been tasked to convert this output to JSON. Is this even possible to do with Oracle 11g? I

Show Detail

How to convert JSON string to JSON in Oracle

I need to convert JSON string to JSON in Oracle query. Example String: {\"1\":{\"qid\":1,\"aid\":1,\"a\":\"Yes\",\"isdyn\":0},\"2\":{\"qid\":2,\"aid\&qu

Show Detail

Convert XML Oracle Form into APEX 21.1

Is it possible to convert the XML version of an Oracle Form into the APEX? I saw a procedure on how to do it in version 20.2 but I'm currently using version 21.1 This is the blog that I saw which ...

Show Detail

How to convert Oracle data to JSON and then use in JavaScript?

Environment is: Oracle APEX v4.2.2 / DB is Oracle 11gR2 / Browser is: IE11 I'm currently working on an application using Oracle APEX v4.2.2 where I need to take data within a table, convert it to ...

Show Detail