|
Subject:
|
XML to Oracle
|
|
Posted By:
|
dpkbahuguna
|
Post Date:
|
9/20/2006 11:33:04 PM
|
Hi all !!
I have txt file and the data in txt file is in XML Format.
And I have to store it all in oracle what should I do for this.
Can somebody help me?
Any Suggestion will be greatly appreciated.
DPK..
|
|
Reply By:
|
woodyz
|
Reply Date:
|
9/21/2006 12:38:18 PM
|
It depends on the size of the file, and how you want to work with the data once it is in the database.
You can simply store it as a string in a VarChar2 field if the size of the string is small, or as a CLOB if it is large.
If you need it to be searchable and to be able to do queries on it, there are mechanisms in Oracle to deal with this, but I haven't worked with this.
Optionally, the tables could be set up to store the data that the XML file contains rather than the XML itself.
Woody Z http://www.learntoprogramnow.com
|
|
Reply By:
|
dpkbahuguna
|
Reply Date:
|
9/22/2006 1:27:31 AM
|
Thanks woodyz !
I will check
thanks for reply
DPK..
|
|
Reply By:
|
dpkbahuguna
|
Reply Date:
|
9/23/2006 12:06:35 AM
|
Hi woodyz !!
I have a some sql code something related to My problem can you make
me understand here what is happining here :-
You can store XML as CLOB or XMLType objects.
You can easily load XML files into the database as CLOBs using the DBMS_LOB PL/SQL package - it has a load-from-file procedural call that can be made. Very easy to use.
Some sample code I posted for our developers on our wiki demonstrates the CLOB approach:
code is :-
------------------------------------------------------------------------------
drop table xml_clob;
drop table my_objects;
-- create a table to store a large XML doc in raw format
create table xml_clob ( doc CLOB )
/
-- create such a raw XML document, using the ALL_OBJECTS
-- table
declare
cursor curXML is
select
XMLElement( "ORA_OBJECT",
XMLForest( object_id as "ID",
owner as "OWNER",
object_type as "TYPE",
object_name as "NAME"
)
) as "XML_DATA"
from all_objects
where rownum < 100001;
xml XMLTYPE;
c CLOB;
begin
DBMS_LOB.CreateTemporary( c, TRUE );
open curXML;
loop
fetch curXML into xml;
exit when curXML%NOTFOUND;
DBMS_LOB.WriteAppend( c, LENGTH( xml.GetCLOBVal() ), xml.GetCLOBVal() );
end loop;
close curXML;
insert into xml_clob
values( '<DATASET>'||c||'</DATASET>' );
commit;
end;
/
create table my_objects
(
obj_id number,
obj_owner varchar2(30),
obj_type varchar2(19),
obj_name varchar2(30)
)
/
-- call proc with a raw XML clob and have it insert the contents
-- into a relational table
create or replace procedure InsertObjects( xmldoc IN OUT NOCOPY CLOB ) is
begin
insert
into my_objects
select
ExtractValue( Value(xml), 'ORA_OBJECT/ID'),
ExtractValue( Value(xml), 'ORA_OBJECT/OWNER'),
ExtractValue( Value(xml), 'ORA_OBJECT/TYPE'),
ExtractValue( Value(xml), 'ORA_OBJECT/NAME')
from TABLE(
select
XMLSEQUENCE( Extract( XMLTYPE(xmldoc), 'DATASET/*' ) )
from dual
) XML;
commit;
end;
/
-- the test (processing 10,000 XML rows):
set timing on
declare
c CLOB;
begin
select doc into c from xml_clob;
InsertObjects( c );
end;
/
set timing off
select count(*) from my_objects;
select * from my_objects where rownum < 4;
--eof
----------------------------------------------------------------------------
Waiting for your reply...
DPK..
|
|
Reply By:
|
woodyz
|
Reply Date:
|
9/25/2006 10:35:34 AM
|
Here is very high level pseudo code of what is going on:
The code you show is an example of some features of Oracle for working with XML
1) Drop the tables of the example (incase you are running the example a second time... otherwise the code will fail on trying to create tables that already exist).
2) Create a new table named xml_clob with a single column (of type CLOB) which is used for storing XML
3) Create an XML Document based on the data that is in the ALL_OBJECTS table, which is a system table in oracle. This is being done so you have some example data to work with. This is stored in a variable named "c".
4) Put that XML data into a record in the xml_clob table. So now you hae a single record with an XML document with all the information from your ALL_OBJECTS table. The insert statement:
insert into xml_clob
values( '<DATASET>'||c||'</DATASET>' );
wraps the XML with a root element named DATASET and inserts it into a record.
5) Create a table named my_objects with 4 columns to hold the data from the XML after processing of the XML
6) Create a stored procedure named InsertObjects that has one parameter which is used to pass in an XML string. This procedure is where all of the actual work of handling the XML is done. It uses an "Insert Into" to put records into the my_objects table. The data that is inserted it extracted from the XML string that is passed in.
The lines that look like this:
ExtractValue( Value(xml), 'ORA_OBJECT/ID'),
are using XML paths to identify the elements for the data values that are being inserted, and the following code:
TABLE(
select
XMLSEQUENCE( Extract( XMLTYPE(xmldoc), 'DATASET/*' ) )
from dual
) XML;
makes a temporary table named XML. I have not used the XMLSEQUENCE method, but I am assuming that it takes the results of the Extract method (which is operating on the elements contained in the xmldoc parameter passed in to the stored proc) and formats them as records in a table.
6) This code reads the XML from the xml_clob table record and calls the InsertObjects stored procedure passing the xml string as an argument.
-- the test (processing 10,000 XML rows):
set timing on
declare
c CLOB;
begin
select doc into c from xml_clob;
InsertObjects( c );
end;
/
7) Finally, the following code does a count on the table to show how many records were adeed.
select count(*) from my_objects
Overall, this is interesting code, and shows how XML can be dealt with in an oracle database. However, the xml data is VERY SIMPLE and essentially is a representation of a database table. Often XML will be very complex compared to this, with nested elements and values in attributes as well. As long as your xml is formatted to represent a table this should work.
On the other hand, if your xml is more complex there are probably ways you can deal with this in Oracle PL/SQL... it just isn't something I have worked with or can help with. However, I've done lots of this sort of thing with VB and C# (and other languages as well). You typically will work with objects that are provided by the language or by components that are available to you such as the msxml components used in VB6 or the System.XML classes in .NET
So... hopefuly that helps a bit.
Woody Z http://www.learntoprogramnow.com
|
