Oracle 19c Inserting data from JSON into a table in PL/SQL

user_29061 0
Viewed 535

I have some existing working PL/SQL code running in a 19c Oracle database that uses the dbms_xmlstore.insertxml(...) to insert data into a table. For this to work the XML is structured so that the element names match the table column names, so the XML looks something like this:

<ROWSET>
  <ROW>
    <EMPLOYEE_ID>920</EMPLOYEE_ID>
    <SALARY>1800</SALARY>
  </ROW>
  <ROW>
    <EMPLOYEE_ID>921</EMPLOYEE_ID>
    <SALARY>2000</SALARY>
  </ROW>
</ROWSET>

I now need to do something similar but with JSON data so is there an something equivalent that allows me to insert rows into a table directly from the JSON object or to to convert the JSON to XML and carry on with the present working code?

EDIT: The key part that I need to replicate is the way the insertxml() function takes the column names from the XML without me needing to supply them to the function.

plsql oracle xml json
1 Answers

Yes, you can use the JSON_TABLE command as part of an INSERT statement. Like this:

INSERT INTO employees
SELECT *
FROM JSON_TABLE('{employees:[
                   {employee_id: 123, salary: 100000}, 
                   {employee_id: 234, salary: 90000}
                 ]}',
                '$.employees[*]'
                COLUMNS (employee_id NUMBER PATH '$.employee_id', 
                         salary NUMBER PATH '$.salary'));

I created a dbFiddle for this: Link

user_22997600
Related