SQL> create user xmluser identified by xmluser;
User created.
SQL> grant connect,resource to xmluser;
Grant succeeded.
SQL>
As xmluser create the following tables
SQL> create table employee
(
id number primary key,name varchar2(20),
regdate date default sysdate
);
Table created.
SQL> create table xml_out ( xml_data clob);
Table created.
The table employee contains the employee data and the table xml_out contains the query result from employee table.
Put few row in employee as follows
SQL> insert into employee(id,name) values(1,'Michael Georgiou');
1 row created.
SQL> insert into employee values(2,'Katerina Georgiou',sysdate+1);
1 row created.
SQL> insert into employee(id) values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace procedure load_xml_from_query is
2 begin
3 declare
4 qryCtx dbms_xmlgen.ctxHandle;
5 result CLOB;
6 begin
7 qryCtx := dbms_xmlgen.newContext('Select id,name,regdate from employee');
8 --Row header is the employee
9 dbms_xmlgen.setRowTag(qryCtx,'Employee');
10 result:= dbms_xmlgen.getXML(qryCtx);
11
12 insert into xml_out values(result);
13
14 dbms_xmlgen.closeContext(qryCtx);
15 end;
16 end;
17 /
Procedure created.
SQL> show errors;
No errors.
SQL> exec load_xml_from_query;
PL/SQL procedure successfully completed.
Now query the xml_out table to see the xml output.
SQL> set long 2000
SQL> set pagesize 80
SQL> select * from xml_out;
XML_DATA
--------------------------------------------------------------------------------
As you can see the xml_data column contain your query in the xml format.
In order to transport the data from xml_data column is good idea to put it in a operating system file.
Create the an oracle directory as follows , login as DBA
Sql > create or replace directory XML_DIR as ‘C:\XML’;
Sql> grant read,write on directory XML_DIR to xmluser;
After that login as xmluser and run the following;
DECLARE
CURSOR xml_data IS SELECT * FROM xml_out;
file_handle utl_file.file_type;
BEGIN
file_handle := utl_file.fopen('XML_DIR','employee.xml','w');
FOR i IN xml_data LOOP
file_handle := utl_file.fopen('XML_DIR','employee.xml', 'a');
utl_file.put_line(file_handle,i.xml_data);
utl_file.fclose(file_handle);
END LOOP;
END;
/
If the above block runs successfully then a new file will be created under
c:\xml with the name employee.xml;
Loading method ( From xml file to table)
You need to load the data in employee.xml in employeeTest table
As xmluser create the following table
SQL> create table employeeTest
(
id number primary key,name varchar2(20),
regdate date default sysdate
);
Where the employee.xml contents is :
The following code insert the above xml into employeeTest table
declare
indoc VARCHAR2(2000);
buf VARCHAR2(30000);
insCtx DBMS_XMLSave.ctxType;
rows number;
name VARCHAR2(20);
dlob CLOB := EMPTY_CLOB();
begin
dbms_lob.createtemporary( dlob, TRUE, 2 );
-- Change the directory to reflect yours
indoc :='C:\XML\employee.xml';
name := 'employeeTest';
xmldom.writetoClob(xmlparser.parse(indoc), dlob);
insCtx := DBMS_XMLSave.newContext(name); -- get the context handle
DBMS_XMLSave.setDateFormat(insCtx, 'dd-MMM-yy');
DBMS_XMLSave.setRowTag(insCtx, 'EMPLOYEE');
rows := DBMS_XMLSave.insertXML(insCtx,dlob); -- this inserts the document
dbms_output.put_line(to_char(rows) ' rows inserted');
DBMS_XMLSave.closeContext(insCtx);
End;
/
*** The EMPLOYEE is case sensitive
SQL> select * from employee;
ID NAME REGDATE
---------- -------------------- ---------
1 Michael Georgiou 22-MAR-06
2 Katerina Georgiou 23-MAR-06
3 22-MAR-06
References
+Oracle9i XML Database Developer’s Guide - Oracle XML DB
+ For generating XML from relational data see chapter 10 in particular:
-Chapter 10 Generating XML Data from the Database
+For loading XML into the database you can either store the data in a CLOB or an XMLTYPE column which is advised. See:
-Chapter 4 Using XMLType
Technical white papers about usage of XDB can also be found on the Technology Network.
http://www.oracle.com/technology/tech/xml/xmldb/index.html
How to Read XML File Stored in Clob Column and Extract Data to Different Table
Note:135352.1
XMLGEN API Has Been Deprecated from XDK 9.2
Note:220020.1
240097.1
DBMS_XMLSave.insertXML Fails With ORA-29532 ORA-06512
185257.1
