Home » SQL & PL/SQL » SQL & PL/SQL » XMLTYPE in Oracle (10g)
XMLTYPE in Oracle [message #402664] Mon, 11 May 2009 23:47 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,

I have oracle table with plain data of around 12,000 records. Now i want to insert total plain data into another oracle table having some normal columns and XMLTYPE column.
Can you tell me the query how to insert data from ordinary oracle table into another having XMLTYPE column.

Regards,
Madhu.
Re: XMLTYPE in Oracle [message #402665 is a reply to message #402664] Mon, 11 May 2009 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: XMLTYPE in Oracle [message #402666 is a reply to message #402665] Tue, 12 May 2009 00:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.orafaq.com/forum/t/141308/0/


http://download-uk.oracle.com/docs/cd/A97630_01/appdev.920/a96620/xdb04cre.htm

See the above mentioned URL`s.
Search before asking.Use XMLtype() constructor.

Regards
Sriram

[Updated on: Tue, 12 May 2009 00:12]

Report message to a moderator

Re: XMLTYPE in Oracle [message #402670 is a reply to message #402664] Tue, 12 May 2009 00:22 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>and XMLTYPE column.
You believe XMLTYPE is a valid "column type" & I disagree.

You did not provide DDL showing a table with such a type being created. Please do so.
Re: XMLTYPE in Oracle [message #402673 is a reply to message #402670] Tue, 12 May 2009 00:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> create table a (id number, col_b xmltype);

Table created.

SQL> desc a
 Name                             Null?    Type
 -------------------------------- -------- ------------
 ID                                        NUMBER
 COL_B                                     XMLTYPE
Re: XMLTYPE in Oracle [message #402676 is a reply to message #402673] Tue, 12 May 2009 00:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
>and XMLTYPE column.
You believe XMLTYPE is a valid "column type" & I disagree.


but Why?


Quote:
Oracle9i Release 1 (9.0.1) introduced a new datatype, XMLType, to facilitate native handling of XML data in the database

Read the below link.
http://download-uk.oracle.com/docs/cd/A97630_01/appdev.920/a96620/xdb04cre.htm

Smile

[Updated on: Tue, 12 May 2009 00:56] by Moderator

Report message to a moderator

Re: XMLTYPE in Oracle [message #402726 is a reply to message #402670] Tue, 12 May 2009 03:49 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,

The structure of normal oracle table is
Name Null? Type
----------------------------------------- -------- --------------------------
DEPTNO NUMBER(5)
BRANCHID VARCHAR2(2)
EMPNO NUMBER
ENAME VARCHAR2(45)
DESIGNATION VARCHAR2(25)
DOJ VARCHAR2(Cool
SALARY NUMBER
H VARCHAR2(25)
STREET VARCHAR2(25)
CITY VARCHAR2(25)
STATE VARCHAR2(25)
COUNTRY VARCHAR2(25)
PHNO VARCHAR2(25)
EMAIL VARCHAR2(25)


The structure of table having XML TYPE is
DEPTNO NUMBER(5)
BRANCHID VARCHAR2(2)
EMPDETAILS XMLTYPE

In Empdetails, the elements are
EMPNO,ENAME,DESIGNATION,DOJ,H,STREET,CITY,STATE,COUNTRY,
PHNO,EMAIL


I think this informatio is sufficient.

Regards,
Madhu.
Re: XMLTYPE in Oracle [message #402730 is a reply to message #402726] Tue, 12 May 2009 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the DDL for both tables, post insert statements for data source table.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format, use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Tue, 12 May 2009 04:04]

Report message to a moderator

Re: XMLTYPE in Oracle [message #402742 is a reply to message #402726] Tue, 12 May 2009 05:02 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Something like this should do the trick.
insert into <table>
select deptno
      ,branchid
      ,xmlelement("Row" ,xmlforest(EMPNO,ENAME,DESIGNATION,DOJ,H,STREET,CITY,STATE,COUNTRY,PHNO,EMAIL))
from <other table>    
Previous Topic: Displaying all columns in SELECT statement, but group by few
Next Topic: distinct values from varray
Goto Forum:
  


Current Time: Sat Dec 03 20:06:00 CST 2016

Total time taken to generate the page: 0.10863 seconds