0

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;

Priyanka Mokashi
  • 407
  • 1
  • 6
  • 14
  • 1
    Possible duplicate of [How can i convert xml to json in oracle?](https://stackoverflow.com/questions/44673405/how-can-i-convert-xml-to-json-in-oracle) – OldProgrammer Sep 27 '18 at 18:29
  • @OldProgrammer I have already tried that one which is not working in my case.. trying to find any other way around. – Priyanka Mokashi Sep 27 '18 at 18:35
  • What does "not working" mean? – OldProgrammer Sep 27 '18 at 18:55
  • 1
    After executing SELECT JSON_OBJECTAGG( id VALUE text ) FROM XMLTABLE( '/xml/*' PASSING XMLTYPE( 'himanshu') COLUMNS id VARCHAR2(200) PATH './name()', text VARCHAR2(200) PATH './text()' ); Getting error : ORA-00907: missing right parenthesis – Priyanka Mokashi Sep 27 '18 at 19:09
  • 1
    What Oracle version are you using? Probably less than 12.2, so as the link says you could create a java function to do it. – kfinity Sep 27 '18 at 20:17
  • @kfinity Thanks for your suggestion :). 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? – Priyanka Mokashi Sep 28 '18 at 06:26

0 Answers0