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

__________________
DPK..

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

Thanks woodyz !

  I will check

thanks for reply

DPK..

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

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:


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.

7) Finally, the following code does a count on the table to show how many records were adeed.

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