Home » SQL & PL/SQL » SQL & PL/SQL » Oracle_XMLTYPE (10g)
Oracle_XMLTYPE [message #403356] Fri, 15 May 2009 00:43 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I want to store XMLfile with bulk data in oracle table. I follow the pl/sql program in the following way to insert the data manuaaly.
declare
v_xml SYS.XMLTYPE;
a clob;
begin
a:=
('<Employees>
<Emp><Empno>16</Empno><Ename>Sudheer Kumar K</Ename><Designation>Clerk</Designation><DOJ>14121982</DOJ><Salary>12000</Salary><Address><H.NO>95 Worli Seaface</H.NO><Street>Worli</Street><City>Mumbai</City><State>Maharashtra</State><Country>India</Country><Phno>+91 98567896</Phno><Email>s199@quantum-usa.com</Email></Address><BranchID>M1</BranchID><Deptno>10</Deptno></Emp>..........................
But it allows upto 32767 bytes. Is there any method to do so?
Regards,
Madhavi.
Re: Oracle_XMLTYPE [message #403358 is a reply to message #403356] Fri, 15 May 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you will not type the xml on your keyboard, so you have to tell us how do you get it?

Regards
Michel
Re: Oracle_XMLTYPE [message #403371 is a reply to message #403356] Fri, 15 May 2009 03:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You only have a 32767 limit on quote delimited strings.
You can have something like:
DECLARE
  v_clob      clob;
BEGIN
  v_clob := to_clob('<32,000 chrs>');
  v_clob := v_clob || to_clob ('<another 32,000 chrs>');
  v_clob := v_clob || to_clob ('<yet another 32,000 chrs>');
Re: Oracle_XMLTYPE [message #403372 is a reply to message #403358] Fri, 15 May 2009 03:22 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
First i loaded the around 13000 records into oracle table (Plain data)by using a procedure with for loop. Then i select the data as xml data and store it in text file. Now i want to insert xmldata into a column having xmltype datatype as one xml file with one ID.

Regards,
Madhavi.
Re: Oracle_XMLTYPE [message #403373 is a reply to message #403372] Fri, 15 May 2009 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DBMS_LOB.LOADCLOBFROMFILE to load the file into a CLOB variable then XMLTYPE constructor to convert and insert it into the target table.

Regards
Michel
Re: Oracle_XMLTYPE [message #403375 is a reply to message #403372] Fri, 15 May 2009 04:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why don't you convert the data to an XMLTYPE when you select it? That would seem to be a good place to do it.
Re: Oracle_XMLTYPE [message #403392 is a reply to message #403375] Fri, 15 May 2009 05:42 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You could

CREATE TABLE XMLTABLE AS
SELECT COLUMN_VALUE AS xmlcol
  FROM TABLE
          (XMLSEQUENCE (XMLTYPE (BFILENAME ('your directory',
                                            'yourfile.xml'
                                           ),
                                 NLS_CHARSET_ID ('WE8ISO8859P1')
                                )
                       )
          );
Previous Topic: How to get sql query output
Next Topic: CONNECT BY Issue
Goto Forum:
  


Current Time: Thu Dec 08 18:35:00 CST 2016

Total time taken to generate the page: 0.05439 seconds