Home » Developer & Programmer » JDeveloper, Java & XML » Fetching/Selecting data from XMLColumns in Oracle (XML)
icon4.gif  Fetching/Selecting data from XMLColumns in Oracle [message #393892] Tue, 24 March 2009 16:57
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

Hi There,

I guess,this is a basic question but I have a problem in displaying and updating XMLTYPE columns in oracle.

Query I am using to display
select extract(details,'Instr/DerivedData/InstdCdtAmt','xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"')
from
instruction;

I was not success in displaying data for a single element in this details column....please,help me... Embarassed



instruction.Details Column (XMLTYPE)
==================
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Instr xmlns="com.clear2pay.bph" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="com.clear2pay.bph BPH-Instruction.xsd">
<InstrRef>test</InstrRef>
<ReqdExctnDt>2008-11-10</ReqdExctnDt>
<InitgPty>
<Nm>Unbal, Same Day</Nm>
<PstlAdr>
<AdrLine>345 STREET</AdrLine>
<PstCd>30043</PstCd>
<TwnNm>BIRMINGHAM</TwnNm>
<CtrySubDvsn>MN</CtrySubDvsn>
<Ctry>US</Ctry>
</PstlAdr>
<Id>
<Id>2222222222</Id>
</Id>
</InitgPty>
<DerivedData>
<InstdDbtAmt>0.00</InstdDbtAmt>
<InstdCdtAmt>50.00</InstdCdtAmt>
</DerivedData>
<RegionalExtensions>
<UnitedStates>
<AcctId>1001</AcctId>
<StandardEntryClass>IAT</StandardEntryClass>
<OriginatingDFI>
<Nm>BP Bank</Nm>
<Id>
<Id>053000196</Id>
</Id>
<PstlAdr>
<PstCd>12345</PstCd>
<TwnNm>San Fransisco</TwnNm>
<Ctry>US</Ctry>
</PstlAdr>
</OriginatingDFI>
<ISODestinationCountryCode>MX</ISODestinationCountryCode>
<ISODestinationCurrencyCode>MXN</ISODestinationCurrencyCode>
<ISOOriginatingCurrencyCode>USD</ISOOriginatingCurrencyCode>
<TemplateLimit>999999.00</TemplateLimit>
<TemplateOwner>1000</TemplateOwner>
<CreatedFromTemplate>false</CreatedFromTemplate>
<TemplateKey>2512</TemplateKey>
<TemplateName>I6 testing</TemplateName>
</UnitedStates>
</RegionalExtensions>
</Instr>
Previous Topic: How can I create "Vacation Request" on JDeveloper
Next Topic: XML parsing in PL/SQL - namespace aware off
Goto Forum:
  


Current Time: Thu Aug 21 21:38:45 CDT 2014

Total time taken to generate the page: 0.13421 seconds