CREATE TABLE tablexml1 ( xml_data XMLTYPE) ;
create directory xml_dir as 'G:\';
Demo xml data
<records><record><column1>Value1</column1><column2>123</column2></record><record><column1>Value2</column1><column2>456</column2></record></records>
create or replace PROCEDURE load_xml (p_dir IN VARCHAR2,p_filename IN VARCHAR2) ASl_bfile BFILE := BFILENAME(p_dir, p_filename);l_clob CLOB;l_dest_offset INTEGER := 1;l_src_offset INTEGER := 1;l_bfile_csid NUMBER := 0;l_lang_context INTEGER := 0;l_warning INTEGER := 0;BEGINDBMS_LOB.createtemporary (l_clob, TRUE);DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);-- loadfromfile deprecated.-- DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));DBMS_LOB.loadclobfromfile (dest_lob => l_clob,src_bfile => l_bfile,amount => DBMS_LOB.lobmaxsize,dest_offset => l_dest_offset,src_offset => l_src_offset,bfile_csid => l_bfile_csid ,lang_context => l_lang_context,warning => l_warning);DBMS_LOB.fileclose(l_bfile);INSERT INTO tablexml1(xml_data)VALUES (XMLTYPE.createXML(l_clob));--delete tablexml2;/*insert into tablexml2SELECT xt.*FROM tablexml1 x,XMLTABLE('/records/record'PASSING x.xml_dataCOLUMNScolumn1 VARCHAR2(10) PATH 'column1',column2 number PATH 'column2') xt ;delete tablexml1;*/COMMIT;DBMS_LOB.freetemporary (l_clob);END load_xml;
SELECT xt.*
FROM tablexml1 x,
XMLTABLE('/records/record'
PASSING x.xml_data
COLUMNS
column1 VARCHAR2(4) PATH 'column1',
column2 VARCHAR2(10) PATH 'column2'
) xt ;
exec load_xml('XML_DIR','data_file.xml');
SELECT XMLELEMENT("Emp", XMLELEMENT("name", e.first_name ||' '|| e.last_name),
XMLELEMENT ( "hiredate", e.hire_date)) AS "result"
FROM employees e
WHERE employee_id > 200 ;
JSON
CREATE OR REPLACE PROCEDURE get_customer_json (
customer_id IN NUMBER,
json_output OUT CLOB -- OUT parameter for JSON output
) IS
BEGIN
SELECT JSON_OBJECT(
'Customer_Name' VALUE c.cust_name,
'Customer_ID' VALUE c.cust_id,
'Accounts' VALUE JSON_ARRAYAGG(a.acct_id)
)
INTO json_output
FROM customers c, accounts a
WHERE c.cust_id =a.cust_id
GROUP BY c.cust_name, c.cust_id;
END;
⚡ Call Procedure ⚡
DECLARE
l_json CLOB;
BEGIN
get_customer_json(1, l_json);
DBMS_OUTPUT.PUT_LINE(l_json);
END;
⚡ Output ⚡
{"Customer_Name":"Alice","Customer_ID":1,"Accounts":[101,102]}
customer_id IN NUMBER,
json_output OUT CLOB -- OUT parameter for JSON output
) IS
BEGIN
SELECT JSON_OBJECT(
'Customer_Name' VALUE c.cust_name,
'Customer_ID' VALUE c.cust_id,
'Accounts' VALUE JSON_ARRAYAGG(a.acct_id)
)
INTO json_output
FROM customers c, accounts a
WHERE c.cust_id =a.cust_id
GROUP BY c.cust_name, c.cust_id;
END;
⚡ Call Procedure ⚡
DECLARE
l_json CLOB;
BEGIN
get_customer_json(1, l_json);
DBMS_OUTPUT.PUT_LINE(l_json);
END;
⚡ Output ⚡
{"Customer_Name":"Alice","Customer_ID":1,"Accounts":[101,102]}
