Search This Blog

Friday, April 13, 2007

XML IN- XML OUT in Oracle 9i

As DBA user create the xmluser just execute the following

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
--------------------------------------------------------------------------------



1
Michael Georgiou
22-MAR-06


2
Katerina Georgiou
23-MAR-06


3
22-MAR-06



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 :




1
Michael Georgiou
22-MAR-06


2
Katerina Georgiou
23-MAR-06


3
22-MAR-06



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