OraFAQ Forum: JDeveloper, Java & XML » Extract data from XML

Home » Developer & Programmer » JDeveloper, Java & XML » Extract data from XML (Oracle 9i)

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

Extract data from XML [message #486800]

Mon, 20 December 2010 06:08

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..

Report message to a moderator

Re: Extract data from XML [message #486805 is a reply to message #486800]

Mon, 20 December 2010 06:20

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

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

Report message to a moderator

Re: Extract data from XML [message #486808 is a reply to message #486805]

Mon, 20 December 2010 06:26

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

Report message to a moderator

Re: Extract data from XML [message #486812 is a reply to message #486808]

Mon, 20 December 2010 06:35

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

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

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.

Report message to a moderator

Re: Extract data from XML [message #486816 is a reply to message #486813]

Mon, 20 December 2010 07:51

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

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

Report message to a moderator

Re: Extract data from XML [message #486860 is a reply to message #486816]

Mon, 20 December 2010 21:49

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();

Report message to a moderator

Re: Extract data from XML [message #486863 is a reply to message #486860]

Mon, 20 December 2010 23:28

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

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

_jum
Messages: 577
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 17:23:58 CST 2025