Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to convert JSON into XML in Oracle 10g

User_RFKSXNov 30 2020

Hi,
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 installing the APEX_JSON package, has anyone here some plsql or other ways to convert JSON into XML and eventually XML into JSON? Because whenever I have to load an external JSON file into a CLOB column I'll have then to convert it into an XML for managing it by using the XML built-in function in Oracle 10g.

Thanks in advance!
Mark

Comments

Solomon Yakobson

You can find PLJSON package on GitHub.
SY.

1 person found this helpful
User_RFKSX

Thank you very much!!
I'll try ...

Solomon Yakobson

Here is example:

SQL> select version from v$instance
  2  /

VERSION
-----------------
10.2.0.5.0

declare
    v_json json := json('{
                          "EMPLOYEE":[
                                      {"NAME":"CLARK","JOB":"MANAGER","SALARY":2450},
                                      {"NAME":"KING","JOB":"PRESIDENT","SALARY":5000},
                                      {"NAME":"MILLER","JOB":"CLERK","SALARY":1300}
                                     ]
                         }'
                       );
    v_xml xmltype;
begin
    v_xml := json_xml.json_to_xml(v_json,'EMPLOYEES');
    dbms_output.put_line(dbms_xmlgen.convert(v_xml.getclobval(),1));
end;
/
<?xml
version="1.0"?><EMPLOYEES><EMPLOYEE><NAME>"CLARK"</NAME><JOB>"MANAGER"</JOB><SAL
ARY>2450</SALARY></EMPLOYEE><EMPLOYEE><NAME>"KING"</NAME><JOB>"PRESIDENT"</JOB><
SALARY>5000</SALARY></EMPLOYEE><EMPLOYEE><NAME>"MILLER"</NAME><JOB>"CLERK"</JOB>
<SALARY>1300</SALARY></EMPLOYEE></EMPLOYEES>

PL/SQL procedure successfully completed.

SQL>

SY.

1 person found this helpful
1 - 3

Post Details

Added on Nov 30 2020
3 comments
4,587 views