CODEWITHSUNDEEP
sqlxmloracle-databaseimportxmltable
Gok
Gok

Reputation: 495

Generic XMLTable Query for data import in Oracle SQL

I want to create Import query which should be taking data from XML file and want to define columns dynamically .

Like same Import XMLtable query can be used for Multi table data imports.

Table1: having 2 columns entity_name trade_date

Table 2 : having 3 columns completely different from table 1

cust_details cust_phone cust_address

For Table 1 XML import query -----

select xt.* from xml_tab x, XMLTABLE('rowset/row' passing x.xml_date columns "ENTITY_NAME" varchar2(10) PATH 'entity_name', "TRADE_DATE" varchar2(10) PATH 'trade_date' ) xt

Want to make XMLTABLE query which will generic for multiple tables ?

Upvotes: 0

Views: 498

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

1) If you want to import data you can user dbms_store or dbms_save. 

create table test123 as select * from dict where 1 =2;

create table test567 as select * from user_tab_privs  where 1 =2;

dbms_xmlgen.getXml - it is creating xml from curosr; 

declare
  v_ctx dbms_xmlsave.ctxType;
  v_rows number;
BEGIN
  v_ctx := dbms_xmlsave.newContext('TEST123'); 
  v_rows := dbms_xmlsave.insertxml(v_ctx,dbms_xmlgen.getXml('select * from dict where rownum<10'));
  dbms_xmlsave.closeContext(v_ctx);
  dbms_output.put_line(v_rows || ' rows inserted...');

   v_ctx := dbms_xmlsave.newContext('TEST567'); 
  v_rows := dbms_xmlsave.insertxml(v_ctx,dbms_xmlgen.getXml('select * from user_tab_privs where rownum<10'));
  dbms_xmlsave.closeContext(v_ctx);
  dbms_output.put_line(v_rows || ' rows inserted...');
  commit;
END;
/

2) You can use positional access of xml elements in xmltable. it is useful in some cases but it is not the best solutions

select *
  from xmltable('ROWSET/ROW'
                passing xmltype(dbms_xmlgen.getXml('select * from dict where rownum<10'))
                columns col1 varchar2(4000) path '*[1]'
                      , col2 varchar2(4000) path '*[2]'
                      , col3 varchar2(4000) path '*[3]'                        
                      , col4 varchar2(4000) path '*[4]'
                      , col5 varchar2(4000) path '*[5]'
                      ---... and more
                       );

                       select *
  from xmltable('ROWSET/ROW'
                passing xmltype(dbms_xmlgen.getXml('select * from user_objects where rownum<10'))
                columns col1 varchar2(4000) path '*[1]'
                      , col2 varchar2(4000) path '*[2]'
                      , col3 varchar2(4000) path '*[3]'                        
                      , col4 varchar2(4000) path '*[4]'
                      , col5 varchar2(4000) path '*[5]'
                      --... and more

Upvotes: 1

Related Questions