How to convert JSON into XML in Oracle 10g

Question

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

Answer 1

You can use the pljson package to parse JSON data.

For example, this question gives an example of how to parse JSON and extract values using the pljson package. Like this:

DECLARE
  obj pljson := pljson(
    '{
      "DASHBOARD": {
        "userUid": "",
        "DATA_DASHBOARD": [
          {
            "CLE": "TESTTEST",
            "X": "",
            "Y": "",
            "COL": "",
            "ROW": "",
            "CLASSCOLOR": "",
            "COLORS": ["df","df"],
            "REGROUPEMENT_ID": "",
            "REGROUPEMENT_TEXT": "",
            "REGROUPEMENT_CLASSCOLOR": "",
            "REGROUPEMENT_X": "",
            "REGROUPEMENT_Y": "",
            "REGROUPEMENT_COL": "",
            "REGROUPEMENT_ROW": "",
            "REGROUPEMENT_COLORS": ["d","df"]
          }
        ]
      }
    }'
  );
  test varchar2(255);
  arr  pljson_list;
BEGIN
  test := pljson_ext.get_string( obj, 'DASHBOARD.DATA_DASHBOARD[1].REGROUPEMENT_COLORS[1]');
  DBMS_OUTPUT.PUT_LINE(test);
  arr := pljson_ext.get_json_list( obj, 'DASHBOARD.DATA_DASHBOARD[1].REGROUPEMENT_COLORS');
  arr.print;
  FOR i IN 1 .. arr.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( arr.get_string(i) );
  END LOOP;
END;
/

(Note: the objects/packages have the pl prefix as db<>fiddle does not allow creating synonyms; you should be able to remove those prefixes if your implementation has the appropriate synonyms created.)