OraFAQ Forum: JDeveloper, Java & XML » xmltype conversion

Home » Developer & Programmer » JDeveloper, Java & XML » xmltype conversion

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

xmltype conversion [message #437723]

Tue, 05 January 2010 15:27

anish0403
Messages: 12
Registered: December 2009
Location: memphis

Junior Member

Hi All,

I have problem in extracting xml type into varibles in pl/sql procedure.

My xml document is something like this

....
...
<Service Result>
<Code> 123 </Code>
<Data> abc</Data>
</Service Result>
<Service Fault >
<Faults count="3">
<Code> 900 </Code>
<Desc> xxxxxxxxx</Desc>
</Faults>
<Faults>
<Code> 1000 </Code>
<Desc> yyyyy</Desc>
</Faults>
<Faults>
<Code> 2000 </Code>
<Desc> yddyyyy</Desc>
</Faults>
</Service Fault>
..........
.......

I am using extract function to get values of Service Result like below:

p_sr_code :=RESPONSE.extract('/doSingleRecordResponse/doSingleRecordResult/ServiceResult/Code/text()','').getStringVal;

p_sr_data :=RESPONSE.extract('/doSingleRecordResponse/doSingleRecordResult/ServiceResult/data/text()', '').getStringVal;

if i want to get values of service fault tag , i have 3 faults here in theabove example . I tried using for loop like below:

p_ad_fcount :=RESPONSE.extract('/doSingleRecordResponse/doSingleRecordResult/Service Fault/Faults/@count','').getStringVal;

FOR i in 1 .. p_ad_fcount
LOOP
p_ad_code(i) :=RESPONSE.extract('/doSingleRecordResponse/doSingleRecordResult/Service Fault/Faults/code/text()',
'').getStringVal;
p_ad_desc(i) :=RESPONSE.extract('/doSingleRecordResponse/doSingleRecordResult/Service Fault/Faults//desc/text()',''
).getStringVal;

end loop;

But it was throwing error :

Can anyone please help me how to retrieve these kinds of tags into varibles?

Note: i declayred p_ad_code &p_ad_desc as varrays .
END IF;

Report message to a moderator

Re: xmltype conversion [message #437797 is a reply to message #437723]

Wed, 06 January 2010 01:10

_jum
Messages: 577
Registered: February 2008

Senior Member

I had slightly to modify Your XML, hope this simple example helps:

WITH DATA AS
     (SELECT XMLTYPE
                ('
<data>
<Service_Result>
  <Code> 123 </Code>
  <Data> abc</Data>
</Service_Result>
<Service_Fault>
  <Faults count="3">
    <Code> 900 </Code>
    <Desc> xxxxxxxxx</Desc>
  </Faults>
  <Faults>
    <Code> 1000 </Code>
    <Desc> yyyyy</Desc>
  </Faults>
  <Faults>
    <Code> 2000 </Code>
    <Desc> yddyyyy</Desc>
  </Faults>
</Service_Fault>
</data>') xml_data
        FROM dual)
SELECT 
  EXTRACTVALUE (VALUE (x), '//Code') codenr,
  EXTRACTVALUE (VALUE (x), '//Desc') descr
  FROM DATA, TABLE (XMLSEQUENCE (EXTRACT (xml_data, '//Faults'))) x

CODENR DESCR
-------------------
900 xxxxxxxxx
1000 yyyyy
2000 yddyyyy

Report message to a moderator

Re: xmltype conversion [message #437907 is a reply to message #437797]

Wed, 06 January 2010 08:50

anish0403
Messages: 12
Registered: December 2009
Location: memphis

Junior Member

if i need to use for loop and retrieve data instead of select query ..in pl.sql procedure , how can i do that?

Report message to a moderator

Re: xmltype conversion [message #437919 is a reply to message #437907]

Wed, 06 January 2010 09:09

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

Senior Member
Account Moderator

What prevent you from looping on a select in PL/SQL?

Regards
Michel

Report message to a moderator

Re: xmltype conversion [message #437924 is a reply to message #437919]

Wed, 06 January 2010 09:16

anish0403
Messages: 12
Registered: December 2009
Location: memphis

Junior Member

FOR i in 1 .. p_ad_fcount
LOOP
p_ad_code(i) :=RESPONSE.extract('/doSingleRecordResponse/doSingleRecordResult/Service Fault/Faults/code/text()',
'').getStringVal;
p_ad_desc(i) :=RESPONSE.extract('/doSingleRecordResponse/doSingleRecordResult/Service Fault/Faults//desc/text()',''
).getStringVal;

I am using the above loop to retrieve data into varray variable , but i am not getting any data, it is erroring out.

Report message to a moderator

Re: xmltype conversion [message #437945 is a reply to message #437924]

Wed, 06 January 2010 10:04

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

Senior Member
Account Moderator

Why don't you want to use jum's solution?

Regards
Michel

Report message to a moderator

Re: xmltype conversion [message #437947 is a reply to message #437945]

Wed, 06 January 2010 10:10

anish0403
Messages: 12
Registered: December 2009
Location: memphis

Junior Member

I didnot get what ihe is trying to say.

Report message to a moderator

Re: xmltype conversion [message #437952 is a reply to message #437947]

Wed, 06 January 2010 10:22

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

Senior Member
Account Moderator

You just have to do
with ...
select ... bulk collect into p_ad_code, p_ad_desc
from ...
instead of the loop and so on.

Regards
Michel

Report message to a moderator

Re: xmltype conversion [message #437959 is a reply to message #437723]

Wed, 06 January 2010 10:48

anish0403
Messages: 12
Registered: December 2009
Location: memphis

Junior Member

Ok . I got you. I never used " with " inside pl/sql so i was confused . When i am researching on xmltypes in oracle ,i cam across one example where we can use indexes with elements if we have more than one element which is pasted below.

You can use the index mechanism to identify individual elements in case of repeated elements in an XML document. For example, if you have an XML document such as:

<PO>
<PONO>100</PONO>
<PONO>200</PONO>
</PO>

you can use:

//PONO[1] to identify the first "PONO" element (with value 100).
//PONO[2] to identify the second "PONO" element (with value 200).

So i was trying to use something like this :

p_ad_code1 :=RESPONSE.extract('/doSingleRecordResponse/doSingleRecordResult/Address/Faults/Fault[1]/code[1]/text()', '').getStringVal;

But it is erroring out. Can you help me why this is erroring out .

my xml is like this :

<Address>
<Faults count =3>
<Fault>
<code>12</code>
<desc>abcd</desc>
</Fault>
<Fault>
<code>34</code>
<desc> tgb </Desc>
</Fault>
</Faults>
</Address>

Why i am getting error(There was an Erorr in address validation -30625 ORA-30625: method dispatch on NULL SELF argument is disallowed) when i try to use index . If this works out then it would be easy for me to use instead of using with statement .

Report message to a moderator

Re: xmltype conversion [message #437965 is a reply to message #437959]

Wed, 06 January 2010 11:03

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

Senior Member
Account Moderator

1/ Your Faults element is not well-formed, value must be enclosed between quotes
2/ Tags are case sensitive, if <desc> then </desc> not </Desc>
3/

SQL> with 
  2    data as ( 
  3      select xmltype(
  4  '<Address>
  5     <Faults count="3">
  6       <Fault>
  7         <code>12</code>
  8         <desc>abcd</desc>
  9       </Fault>
 10       <Fault>
 11         <code>34</code>
 12         <desc> tgb </desc>
 13       </Fault>
 14     </Faults>
 15   </Address>') xml_data 
 16      from dual 
 17    )
 18  select extractvalue(value(x), '/Faults/Fault[1]/code') code1
 19  from data, table(xmlsequence(extract(xml_data, '/Address/Faults'))) x
 20  /
CODE1
--------------------------------------------------------
12

1 row selected.

WITH is just a way to write a subquery.

Regards
Michel

[Updated on: Wed, 06 January 2010 11:03]

Report message to a moderator

Re: xmltype conversion [message #437967 is a reply to message #437723]

Wed, 06 January 2010 11:11

anish0403
Messages: 12
Registered: December 2009
Location: memphis

Junior Member

Ok, now my question is :
I have my xml result in respoinse object which is og xmltype.
Can i write something like below:

WITH DATA AS
(SELECT response xml_data
FROM dual)
SELECT
EXTRACTVALUE (VALUE (x), '//Code') codenr ,
EXTRACTVALUE (VALUE (x), '//Desc') descr bulk collect into p_ad_code,p_ad_desc
FROM DATA, TABLE (XMLSEQUENCE (EXTRACT (xml_data, '//Fault'))) x;

Report message to a moderator

Re: xmltype conversion [message #437969 is a reply to message #437967]

Wed, 06 January 2010 11:22

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

Senior Member
Account Moderator

SQL> declare
  2    response xmltype := xmltype(
  3  '<Address>
  4     <Faults count="3">
  5       <Fault>
  6         <code>12</code>
  7         <desc>abcd</desc>
  8       </Fault>
  9       <Fault>
 10         <code>34</code>
 11         <desc> tgb </desc>
 12       </Fault>
 13     </Faults>
 14   </Address>');
 15    code integer;
 16  begin
 17    with data as (select response xml_data from dual)
 18    select extractvalue(value(x), '/Faults/Fault[1]/code') into code
 19    from data, table(xmlsequence(extract(xml_data, '/Address/Faults'))) x;
 20    dbms_output.put_line ('code is '||code);
 21  end;
 22  /
code is 12

PL/SQL procedure successfully completed.

Regards
Michel

Report message to a moderator

Re: xmltype conversion [message #437974 is a reply to message #437969]

Wed, 06 January 2010 11:43

anish0403
Messages: 12
Registered: December 2009
Location: memphis

Junior Member

Thanks Michel for your time . I will try to do that and thanks for your help .

Report message to a moderator

Re: xmltype conversion [message #438023 is a reply to message #437974]

Wed, 06 January 2010 16:02

anish0403
Messages: 12
Registered: December 2009
Location: memphis

Junior Member

Hi Mich,

I have one more concern .

create or replace procedure p1_ex as

response xmltype ;
TYPE te_sf IS VARRAY(20) OF VARCHAR2(100);
code te_sf :=NULL;
i number :=1;
code1 integer;
begin

response := xmltype('<doSingleRecordResponse xmlns="urn:mdWebService">
<doSingleRecordResult xsi:type="ResponseRecord" id="1" version="2.30" action="Response" timeout="20" timestamp="01-06-2010" transmissionReference="ABCD" TimeToProcess="171.8783" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Address version="2.30">
<Faults count="3">
<Fault>
<Code>pkgAddressMailingEmptyCity</Code>
<Desc>Empty City</Desc>
<Source>/Request/Record/City</Source>
<Detail>mdDQ.mdWebService.RequestRecord.</Detail>
</Fault>
<Fault>
<Code>pkgAddressMailing:EmptyState</Code>
<Desc>Empty State</Desc>
<Source>/Request/Record/State</Source>
<Detail>mdDQ.mdWebService.RequestRecord.</Detail>
</Fault>
<Fault>
<Code>pkgAddressMailing:EmptyZip</Code>
<Desc>Empty Zip</Desc>
<Source>/Request/Record/Zip5</Source>
<Detail>mdDQ.mdWebService.RequestRecord.</Detail>
</Fault>
</Faults>
<Result>
<ErrorCode>Z</ErrorCode>
<ErrorDesc>Invalid ZIP/Postal Code</ErrorDesc>
<StatusCode>X</StatusCode>
<StatusDesc>Address was not coded.</StatusDesc>
</Result>
</Address>
</doSingleRecordResult>
</doSingleRecordResponse>
');

with data as (select response xml_data from dual)
select extractvalue(value(x), '//Desc') bulk collect into code
from data, table(xmlsequence(extract(xml_data, '//Fault','xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:mdWebService"'
))) x;

dbms_output.put_line('xxx');
dbms_output.put_line(code(1));
dbms_output.put_line(code(2));

end;
/

In the above procedure , it is giving the below result :

xxx
xxx
xxx

instead of
xxx
Empty City
Empty State

.
Can you tel me why it is giving output like that .And i noticed that when i ran the below procedure it is giving correct result :

create or replace procedure p1_ex1 as

response xmltype ;
TYPE te_sf IS VARRAY(20) OF VARCHAR2(100);
code te_sf :=NULL;
i number :=1;
code1 integer;
begin

response := xmltype('
<Address version="2.30">
<Faults count="3">
<Fault>
<Code>pkgAddressMailingEmptyCity</Code>
<Desc>Empty City</Desc>
<Source>/Request/Record/City</Source>
<Detail>mdDQ.mdWebService.RequestRecord.</Detail>
</Fault>
<Fault>
<Code>pkgAddressMailing:EmptyState</Code>
<Desc>Empty State</Desc>
<Source>/Request/Record/State</Source>
<Detail>mdDQ.mdWebService.RequestRecord.</Detail>
</Fault>
<Fault>
<Code>pkgAddressMailing:EmptyZip</Code>
<Desc>Empty Zip</Desc>
<Source>/Request/Record/Zip5</Source>
<Detail>mdDQ.mdWebService.RequestRecord.</Detail>
</Fault>
</Faults>
<Result>
<ErrorCode>Z</ErrorCode>
<ErrorDesc>Invalid ZIP/Postal Code</ErrorDesc>
<StatusCode>X</StatusCode>
<StatusDesc>Address was not coded.</StatusDesc>
</Result>
</Address>
');

with data as (select response xml_data from dual)
select extractvalue(value(x), '//Desc') bulk collect into code
from data, table(xmlsequence(extract(xml_data, '//Fault'))) x;

dbms_output.put_line('shilpa');
dbms_output.put_line(code(1));
dbms_output.put_line(code(2));

end;
/

Only difference between these 2 procedure is response object is not having any namespace in first one and second have namespaces.

Do you have any idea why this is givign result like that.

Appreciate your help

Report message to a moderator

Re: xmltype conversion [message #438144 is a reply to message #438023]

Thu, 07 January 2010 04:23

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

Senior Member
Account Moderator

Please format your code, read OraFAQ Forum Guide, "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

The namespace must also been specified on EXTRACTVALUE:

SQL> with data as (
  2   select xmltype('
  3  <doSingleRecordResponse xmlns="urn:mdWebService">
  4  <doSingleRecordResult 
  5   xsi:type="ResponseRecord" id="1" version="2.30" action="Response" timeout="20" timestamp="01-06-2010" 
  6   transmissionReference="ABCD" TimeToProcess="171.8783" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  7  <Address version="2.30">
  8  <Faults count="3">
  9  <Fault>
 10  <Code>pkgAddressMailingEmptyCity</Code>
 11  <Desc>Empty City</Desc>
 12  <Source>/Request/Record/City</Source>
 13  <Detail>mdDQ.mdWebService.RequestRecord.</Detail>
 14  </Fault>
 15  <Fault>
 16  <Code>pkgAddressMailing:EmptyState</Code>
 17  <Desc>Empty State</Desc>
 18  <Source>/Request/Record/State</Source>
 19  <Detail>mdDQ.mdWebService.RequestRecord.</Detail>
 20  </Fault>
 21  <Fault>
 22  <Code>pkgAddressMailing:EmptyZip</Code>
 23  <Desc>Empty Zip</Desc>
 24  <Source>/Request/Record/Zip5</Source>
 25  <Detail>mdDQ.mdWebService.RequestRecord.</Detail>
 26  </Fault>
 27  </Faults>
 28  <Result>
 29  <ErrorCode>Z</ErrorCode>
 30  <ErrorDesc>Invalid ZIP/Postal Code</ErrorDesc>
 31  <StatusCode>X</StatusCode>
 32  <StatusDesc>Address was not coded.</StatusDesc>
 33  </Result>
 34  </Address>
 35  </doSingleRecordResult>
 36  </doSingleRecordResponse>
 37  ') xml_data from dual )
 38  select extractvalue(value(x), '//Desc') 
 39  from data, table(xmlsequence(extract(xml_data, '//Fault', 
 40                   'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:mdWebService"'
 41  ))) x;
EXTRACTVALUE(VALUE(X),'//DESC')
------------------------------------------------------------------------------------------------




3 rows selected.

SQL> with data as (
  2   select xmltype('
  3  <doSingleRecordResponse xmlns="urn:mdWebService">
  4  <doSingleRecordResult 
  5   xsi:type="ResponseRecord" id="1" version="2.30" action="Response" timeout="20" timestamp="01-06-2010" 
  6   transmissionReference="ABCD" TimeToProcess="171.8783" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  7  <Address version="2.30">
  8  <Faults count="3">
  9  <Fault>
 10  <Code>pkgAddressMailingEmptyCity</Code>
 11  <Desc>Empty City</Desc>
 12  <Source>/Request/Record/City</Source>
 13  <Detail>mdDQ.mdWebService.RequestRecord.</Detail>
 14  </Fault>
 15  <Fault>
 16  <Code>pkgAddressMailing:EmptyState</Code>
 17  <Desc>Empty State</Desc>
 18  <Source>/Request/Record/State</Source>
 19  <Detail>mdDQ.mdWebService.RequestRecord.</Detail>
 20  </Fault>
 21  <Fault>
 22  <Code>pkgAddressMailing:EmptyZip</Code>
 23  <Desc>Empty Zip</Desc>
 24  <Source>/Request/Record/Zip5</Source>
 25  <Detail>mdDQ.mdWebService.RequestRecord.</Detail>
 26  </Fault>
 27  </Faults>
 28  <Result>
 29  <ErrorCode>Z</ErrorCode>
 30  <ErrorDesc>Invalid ZIP/Postal Code</ErrorDesc>
 31  <StatusCode>X</StatusCode>
 32  <StatusDesc>Address was not coded.</StatusDesc>
 33  </Result>
 34  </Address>
 35  </doSingleRecordResult>
 36  </doSingleRecordResponse>
 37  ') xml_data from dual )
 38  select extractvalue(value(x), '//Desc', 
 39                      'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:mdWebService"') 
 40  from data, table(xmlsequence(extract(xml_data, '//Fault', 
 41                   'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:mdWebService"'
 42  ))) x;
EXTRACTVALUE(VALUE(X),'//DESC','XMLNS:XSI="HTTP://WWW.W3.ORG/2001/XMLSCHEMA-INSTANCE"XMLNS="URN:MDWEBSERVICE"')
----------------------------------------------------------------------------------------------------
Empty City
Empty State
Empty Zip

3 rows selected.

Regards
Michel

[Updated on: Thu, 07 January 2010 04:23]

Report message to a moderator

Re: xmltype conversion [message #440101 is a reply to message #437723]

Thu, 21 January 2010 01:33

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

Senior Member
Account Moderator

Your new question has been moved to "Problem with varray type out parameter" topic in "SQL & PL/SQL" forum.

Regards
Michel

Report message to a moderator

Previous Topic:	Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 500
Next Topic:	Need help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 17:30:56 CST 2025