CODEWITHSUNDEEP
sqloracle-database
manish
manish

Reputation: 21

Inserting Data in XML into Oracle database

My task is to get data from One database (non oracle db) and insert that data into another database(oracle).

I am able to get the data from the source database in the form of XML. Now I have to pass this XML as input to the oracle database so that all the data inside the XML is inserted into the oracle database table.

Can some one please guide me as what is the code for doing the same. I am quite used to SQL Server 2005.

If some one can guide with how insert data in XMl into a table, it would be of great help.

Upvotes: 2

Views: 6117

Answers (1)

StevieG
StevieG

Reputation: 8709

This is as generic as I can make it without seeing the xml structure..

create or replace procedure put_stuff_into_table(source_xml_doc xmltype) AS

BEGIN

insert into table (a, b)
select *
from xmltable('<TOP_LEVEL_ELEMENT>'
              passing source_xml_doc
              columns a number        path 'ELEMENT_TAG_A',
                      b varchar2(100) path 'ELEMENT_TAG_B'
             );
END;
/

Upvotes: 3

Related Questions