OraFAQ Forum: JDeveloper, Java & XML » Insert Data into XMLTYPE

Home » Developer & Programmer » JDeveloper, Java & XML » Insert Data into XMLTYPE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Insert Data into XMLTYPE [message #388988]

Thu, 26 February 2009 21:25

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

Report message to a moderator

Re: Insert Data into XMLTYPE [message #389003 is a reply to message #388988]

Thu, 26 February 2009 23:04

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Insert Data into XMLTYPE [message #390498 is a reply to message #389003]

Fri, 06 March 2009 11:06

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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 08:07:18 CST 2025