Home » Developer & Programmer » JDeveloper, Java & XML » Insert Data into XMLTYPE
icon4.gif  Insert Data into XMLTYPE [message #388988] Thu, 26 February 2009 21:25 Go to next message
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

Someone can give an Idea ?

Load the source address information into Address Column in target table where the column datatype is defined as XMLTYPE.


source table

empno char
ename char
address1 char
address2 char
city char
state char



Target Table

Empno char
Ename char
Address XMLTYPE



your help on this is much appreciated...

Thanks
Re: Insert Data into XMLTYPE [message #389003 is a reply to message #388988] Thu, 26 February 2009 23:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert into target
select empno, ename,
       xmltype('
<address>
  <address1>'||address1||'</address1>
  <address2>'||address2||'</address2>
  <city>'||city||'</city>
  <state>'||state||'</state>
</address>')
from source
/

This is just an example, it depends on the xml you want, line feed as there just for clarity and can be removed...

Regards
Michel
Re: Insert Data into XMLTYPE [message #390498 is a reply to message #389003] Fri, 06 March 2009 11:06 Go to previous message
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

Thanks Mike...

Can you tell me how to add the namespaces and schema location to the existing code and I am here with pasting the expecting output..



/Trn/@xmlns
XML Namespace - refer to http://www.w3.org/TR/REC-xml-names/
DEFAULT TO 'com.clear2pay.bph'

/Trn/@xmlns:xsi
XML Namespace Schema Instance
DEFAULT TO 'http://www.w3.org/2001/XMLSchema-instance'

/Trn/@xsi:schemaLocation
XML Schema Location
DEFAULT TO 'com.clear2pay.bph BPH-Transaction.xsd'


output


<?xml version="1.0" encoding="UTF-8"?>
<Trn xmlns="com.clear2pay.bph" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="com.clear2pay.bph BPH-Transaction.xsd">
<TrnTp>C</TrnTp>
<PmtId>
<EndToEndId>8403150</EndToEndId>
</PmtId>
<InstdAmt Ccy="USD">201.00</InstdAmt>
<AcctOwner>
<Nm>Belgium Checking</Nm>
<PstlAdr>
<AdrLine>111 Buckingham Palace</AdrLine>
<PstCd>30043</PstCd>
<TwnNm>Huntersville</TwnNm>
<Ctry>BE</Ctry>
</PstlAdr>
<Id>
<Id>132434332</Id>
</Id>
</AcctOwner>

Previous Topic: Got minus one from a read call
Next Topic: Java console access (oracle 9ias running on unix)
Goto Forum:
  


Current Time: Thu Mar 28 17:24:25 CDT 2024