Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 20th, 2006, 11:33 PM
Friend of Wrox
 
Join Date: Jan 2006
Location: Hardwar, Uttranchal, India.
Posts: 180
Thanks: 1
Thanked 1 Time in 1 Post
Send a message via ICQ to dpkbahuguna Send a message via MSN to dpkbahuguna Send a message via Yahoo to dpkbahuguna
Default XML to Oracle

 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..
  #2 (permalink)  
Old September 21st, 2006, 12:38 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old September 22nd, 2006, 01:27 AM
Friend of Wrox
 
Join Date: Jan 2006
Location: Hardwar, Uttranchal, India.
Posts: 180
Thanks: 1
Thanked 1 Time in 1 Post
Send a message via ICQ to dpkbahuguna Send a message via MSN to dpkbahuguna Send a message via Yahoo to dpkbahuguna
Default

Thanks woodyz !

  I will check

thanks for reply

DPK..
  #4 (permalink)  
Old September 23rd, 2006, 12:06 AM
Friend of Wrox
 
Join Date: Jan 2006
Location: Hardwar, Uttranchal, India.
Posts: 180
Thanks: 1
Thanked 1 Time in 1 Post
Send a message via ICQ to dpkbahuguna Send a message via MSN to dpkbahuguna Send a message via Yahoo to dpkbahuguna
Default

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..
  #5 (permalink)  
Old September 25th, 2006, 10:35 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:

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.

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

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Oracle SOA, BPEL and Oracle worklist umeshtheone Pro Java 1 April 16th, 2008 11:01 PM
call oracle function using oracle link server vl SQL Server 2000 1 July 12th, 2007 08:19 AM
How to Read Oracle Data without Oracle being insta badrinarayanang Oracle 1 October 6th, 2005 06:34 AM
Connecting to Oracle without Oracle Client mahaveerar VB Databases Basics 0 February 22nd, 2005 05:18 PM
Connecting to Oracle without Oracle Client mahaveerar Excel VBA 0 February 22nd, 2005 03:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.