Thanks for the question, Manohar.

Asked: August 07, 2020 - 2:15 am UTC

Last updated: August 15, 2020 - 6:43 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hello Everyone,

There is a table(xxln_vs_publish_stg) has xmltype column(xml_data) which stores XML data. I have a requirement to convert XML data to json data.
For that, I am using apex_json.write to convert. While executing below logic for changing it, I am getting Error as: ORA-20987: APEX - JSON.WRITER.NOT_OPEN - Contact your application administrator.
Can you please help what is it I am doing which is wrong.

DECLARE
    l_xml            sys.xmltype;
    l_amount         BINARY_INTEGER := 32000;
    l_buffer         RAW(32000);
    l_pos            INTEGER := 1;
    l_stage          NUMBER;
    content          CLOB;
    content_blob     BLOB;
    content_length   NUMBER;
BEGIN
    SELECT
        xml_data
    INTO l_xml
    FROM
        xxln_vs_publish_stg
    WHERE
        xml_data IS NOT NULL
        AND ROWNUM < 2;

    content := xmltype.getclobval(l_xml);
    xxln.convert_clob_to_blob(content, content_blob);
    content_length := dbms_lob.getlength(content_blob);
    dbms_output.put_line(content_length);
    apex_json.initialize_clob_output;
    IF dbms_lob.getlength(content_blob) < 32000 THEN
        apex_json.write(content);
    ELSE
        WHILE l_pos < content_length--DBMS_LOB.GETLENGTH(v_output_file_blob)
         LOOP
            dbms_lob.read(content_blob, l_amount, l_pos, l_buffer);
            apex_json.write(content);
            l_pos := l_pos + l_amount;
        END LOOP;
    END IF;

    dbms_output.put_line(apex_json.get_clob_output);
    apex_json.free_output;
END;

and Connor said...

This should help fix your error, but I'm not entirely sure it will give you the result you are after

SQL> DECLARE
  2      l_xml            sys.xmltype;
  3      content          CLOB;
  4  BEGIN
  5      SELECT
  6  xmltype('<?xml version="1.0"?>
  7  <ROWSET>
  8    <DEPT>
  9      <DEPTNO>10</DEPTNO>
 10      <DNAME>ACCOUNTING</DNAME>
 11      <LOC>NEW YORK</LOC>
 12      <EMP_LIST>
 13        <EMP_ROW>
 14          <EMPNO>7782</EMPNO>
 15          <ENAME>CLARK</ENAME>
 16          <JOB>MANAGER</JOB>
 17          <MGR>7839</MGR>
 18          <HIREDATE>09-JUN-1981 00:00:00</HIREDATE>
 19          <SAL>2450</SAL>
 20        </EMP_ROW>
 21        <EMP_ROW>
 22          <EMPNO>7839</EMPNO>
 23          <ENAME>KING</ENAME>
 24          <JOB>PRESIDENT</JOB>
 25          <HIREDATE>17-NOV-1981 00:00:00</HIREDATE>
 26          <SAL>5000</SAL>
 27        </EMP_ROW>
 28        <EMP_ROW>
 29          <EMPNO>7934</EMPNO>
 30          <ENAME>MILLER</ENAME>
 31          <JOB>CLERK</JOB>
 32          <MGR>7782</MGR>
 33          <HIREDATE>23-JAN-1982 00:00:00</HIREDATE>
 34          <SAL>1300</SAL>
 35        </EMP_ROW>
 36      </EMP_LIST>
 37    </DEPT>
 38  </ROWSET>')
 39      INTO l_xml
 40      FROM
 41          dual;
 42
 43      content := xmltype.getclobval(l_xml);
 44      apex_json.initialize_clob_output;
 45      apex_json.open_object;             <======
 46      apex_json.write(content);
 47      apex_json.close_object;            <======
 48
 49      dbms_output.put_line(apex_json.get_clob_output);
 50      apex_json.free_output;
 51  END;
 52  /

PL/SQL procedure successfully completed.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It works

Manohar Mukidi, August 10, 2020 - 2:52 pm UTC

Thanks Connor. It works but it has some weird characters in front of every line. Ex:
{
"\u003C?xml version=\"1.0\"?\u003E\n \u003CROWSET\u003E\n \u003CDEPT\u003E\n \u003CDEPTNO\u003E10\u003C\/DEPTNO\u003E\n \u003CDNAME\u003EACCOUNTING\u003C\/DNAME\u003E\n \u003CLOC\u003ENEW YORK\u003C\/LOC\u003E\n \u003CEMP_LIST\u003E\n \u003CEMP_ROW\u003E\n \u003CEMPNO\u003E7782\u003C\/EMPNO\u003E\n \u003CENAME\u003ECLARK\u003C\/ENAME\u003E\n \u003CJOB\u003EMANAGER\u003C\/JOB\u003E\n \u003CMGR\u003E7839\u003C\/MGR\u003E\n \u003CHIREDATE\u003E09-JUN-1981 00:00:00\u003C\/HIREDATE\u003E\n \u003CSAL\u003E2450\u003C\/SAL\u003E\n \u003C\/EMP_ROW\u003E\n \u003CEMP_ROW\u003E\n \u003CEMPNO\u003E7839\u003C\/EMPNO\u003E\n \u003CENAME\u003EKING\u003C\/ENAME\u003E\n \u003CJOB\u003EPRESIDENT\u003C\/JOB\u003E\n \u003CHIREDATE\u003E17-NOV-1981 00:00:00\u003C\/HIREDATE\u003E\n \u003CSAL\u003E5000\u003C\/SAL\u003E\n \u003C\/EMP_ROW\u003E\n \u003CEMP_ROW\u003E\n \u003CEMPNO\u003E7934\u003C\/EMPNO\u003E\n \u003CENAME\u003EMILLER\u003C\/ENAME\u003E\n \u003CJOB\u003ECLERK\u003C\/JOB\u003E\n \u003CMGR\u003E7782\u003C\/MGR\u003E\n \u003CHIREDATE\u003E23-JAN-1982 00:00:00\u003C\/HIREDATE\u003E\n \u003CSAL\u003E1300\u003C\/SAL\u003E\n \u003C\/EMP_ROW\u003E\n \u003C\/EMP_LIST\u003E\n \u003C\/DEPT\u003E\n \u003C\/ROWSET\u003E"
}

August 11, 2020 - 4:08 am UTC

Hence my statement

" but I'm not entirely sure it will give you the result you are after"

This is your XML escaped to be a JSON fragment. This is not a XML-to-JSON conversion facility, it is a create JSON facility.

Take a look here for a tool that may be useful

https://odieweblog.wordpress.com/tag/xml-to-json/

For simpler requirements, using the native JSON/XML facilities might be enough

SQL> SELECT JSON_OBJECTAGG( id VALUE text )
  2  FROM   XMLTABLE(
  3           '/xml/*'
  4           PASSING XMLTYPE( '<xml><name>Connor</name></xml>')
  5           COLUMNS id   VARCHAR2(200) PATH './name()',
  6                   text VARCHAR2(200) PATH './text()'
  7         );
{"name":"Connor"}

Thank you

Manohar Mukidi, August 14, 2020 - 3:42 am UTC

Thanks Connor!!

August 15, 2020 - 6:43 am UTC

glad we could help

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

It works

Thank you

More to Explore

APEX

Converting XML to JSON using Apex

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

It works

Thank you

More to Explore

APEX