Home » Developer & Programmer » JDeveloper, Java & XML » Extract data from XML (Oracle 9i)
Extract data from XML [message #486800] Mon, 20 December 2010 06:08 Go to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

I want to retrieve values from a XML data stored on CLOB data type and store it in different table.

Can someone help me with retrieving data from Soap Envelope and Soap Body?

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope>
<soap:Header>
<company>US</company>
</soap:Header>
<soap:Body>
Data
</soap:Body>
<Account>ABCD1234</Account>
</soap:Envelope>

Thanks a lot..
Re: Extract data from XML [message #486805 is a reply to message #486800] Mon, 20 December 2010 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select xmltype('<?xml version="1.0" encoding="utf-8"?>
  2  <soap:Envelope>
  3  <soap:Header>
  4  <company>US</company> 
  5  </soap:Header>
  6  <soap:Body>
  7  Data
  8  </soap:Body>
  9  <Account>ABCD1234</Account> 
 10  </soap:Envelope>') from dual
 11  /
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00234: namespace prefix "soap" is not declared
Error at line 2
ORA-06512: at "SYS.XMLTYPE", line 301
ORA-06512: at line 1

00234, 00000, "namespace prefix \"~S\" is not declared"
// *Cause:  Namespace prefix is not declared.
// *Action:  Declare the prefix in an attribute list.

Regards
Michel
Re: Extract data from XML [message #486808 is a reply to message #486805] Mon, 20 December 2010 06:26 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Michel,

Thanks a lot for your response.

Please refer to below XML.

select xmltype('<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.o3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.o3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<company>US</company>
</soap:Header>
<soap:Body>
</soap:Body>
<Account>ABCD1234</Account>
<party id="336" type="Person" assoc="RegisteredContact on policy">
<Forename>ABCD</Forename>
</party>
<party id="337" type="Person" assoc="RegisteredContact on policy">
<Forename>ABCDEF</Forename>
</party>
</soap:Envelope>') from dual
Re: Extract data from XML [message #486812 is a reply to message #486808] Mon, 20 December 2010 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now what exactly do you want from this?

Note that there are many examples of getting values from a xml in this JDeveloper, Java & XML forum.

Regards
Michel

[Updated on: Mon, 20 December 2010 06:37]

Report message to a moderator

Re: Extract data from XML [message #486813 is a reply to message #486812] Mon, 20 December 2010 06:46 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

This XML is stored in a CLOB data type and I want to retrieve values from this XML, perform some validation and store in a different table.

I am getting below error.

ORA-30625: method dispatch on NULL SELF argument is disallowed

Thanks.
Re: Extract data from XML [message #486816 is a reply to message #486813] Mon, 20 December 2010 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am getting below error.

It is hard to debug some code we don't see.
Please post what you EXACTLY want from the XML you provided.

Regards
Michel
Re: Extract data from XML [message #486860 is a reply to message #486816] Mon, 20 December 2010 21:49 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Table Tab1 has below value

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:xsi="http://www.o3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.o3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<company>US</company>
</soap:Header>
<soap:Body>
<Account>ABCD1234</Account>
<party id="336" type="Person" assoc="RegisteredContact on policy">
<Forename>ABCD</Forename>
</party>
<party id="337" type="Person" assoc="RegisteredContact on policy">
<Forename>ABCDEF</Forename>
</party>
</soap:Body>
</soap:Envelope>

I want to retrieve value ABCD & ABCDEF from Forename field and store it in a variable, perform some validation and store it in different table.

But when i try to store it in different variable using below command, it gives error.

VC_APPLNNBR := VX_MAINTYPE.EXTRACT('/soap:Envelope/soap:Body/party/Forename/text()').GETSTRINGVAL();
Re: Extract data from XML [message #486863 is a reply to message #486860] Mon, 20 December 2010 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
, it gives error

Please share the error.
Please share the code (not just one line we are not sure the error we don't know, comes from).
Please copy and paste the execution from SQL*Plus we can then see what happen.
Please read OraFAQ Forum Guide, especially "How to post" section.

SQL> col account format a20
SQL> col forename format a20
SQL> with data as (
  2  select xmltype('<?xml version="1.0" encoding="utf-8"?>
  3  <soap:Envelope xmlns:xsi="http://www.o3.org/2001/XMLSchema-instance" 
  4                 xmlns:xsd="http://www.o3.org/2001/XMLSchema" 
  5                 xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  6  <soap:Header>
  7  <company>US</company> 
  8  </soap:Header>
  9  <soap:Body>
 10  </soap:Body>
 11  <Account>ABCD1234</Account> 
 12  <party id="336" type="Person" assoc="RegisteredContact on policy">
 13  <Forename>ABCD</Forename> 
 14  </party>
 15  <party id="337" type="Person" assoc="RegisteredContact on policy">
 16  <Forename>ABCDEF</Forename> 
 17  </party>
 18  </soap:Envelope>') val from dual 
 19    )
 20  select extractvalue(val, '//Account',
 21                      'xmlns:xsi="http://www.o3.org/2001/XMLSchema-instance" 
 22                      xmlns:xsd="http://www.o3.org/2001/XMLSchema" 
 23                      xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"') 
 24           Account,
 25         extractvalue(value(x), '//Forename',
 26                      'xmlns:xsi="http://www.o3.org/2001/XMLSchema-instance" 
 27                      xmlns:xsd="http://www.o3.org/2001/XMLSchema" 
 28                      xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"') 
 29           Forename
 30  from data, table(xmlsequence(extract(val, '//Forename',
 31                      'xmlns:xsi="http://www.o3.org/2001/XMLSchema-instance" 
 32                      xmlns:xsd="http://www.o3.org/2001/XMLSchema" 
 33                      xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'))) x
 34  /
ACCOUNT              FORENAME
-------------------- --------------------
ABCD1234             ABCD
ABCD1234             ABCDEF

Regards
Michel

[Updated on: Tue, 21 December 2010 01:10]

Report message to a moderator

Re: Extract data from XML [message #486878 is a reply to message #486863] Tue, 21 December 2010 01:12 Go to previous message
_jum
Messages: 485
Registered: February 2008
Senior Member
You can simplify the query:
WITH data AS (
    SELECT XMLTYPE(
    '<?xml version="1.0" encoding="utf-8"?>
      <soap:Envelope xmlns:xsi="http://www.o3.org/2001/XMLSchema-instance" 
                     xmlns:xsd="http://www.o3.org/2001/XMLSchema" 
                     xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
       <soap:Header>
         <company>US</company> 
       </soap:Header>
       <soap:Body>
       </soap:Body>
       <Account>ABCD1234</Account> 
       <party id="336" type="Person" assoc="RegisteredContact on policy">
         <Forename>ABCD</Forename> 
       </party>
       <party id="337" type="Person" assoc="RegisteredContact on policy">
         <Forename>ABCDEF</Forename> 
       </party>
     </soap:Envelope>') val FROM dual)
   SELECT extractvalue(val, '//Account') account,
          extractvalue(value(x), '//Forename') forename
     FROM data, table(xmlsequence(extract(val, '//Forename'))) x;

ACCOUNT              FORENAME
-------------------- --------------------
ABCD1234             ABCD
ABCD1234             ABCDEF


Previous Topic: cannot access jxl.Workbook
Next Topic: Improve the performance of XMLConcat
Goto Forum:
  


Current Time: Fri Apr 18 03:37:10 CDT 2014

Total time taken to generate the page: 0.12037 seconds