OraFAQ Forum: JDeveloper, Java & XML » Need a Procedure for reading xml element value

Home » Developer & Programmer » JDeveloper, Java & XML » Need a Procedure for reading xml element value (Oracle 10g)

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

Need a Procedure for reading xml element value [message #508940]

Wed, 25 May 2011 06:10

sangakirankumar
Messages: 18
Registered: August 2008
Location: india

Junior Member

Hi,

In my pl/sql procedure I'm calling a webservice and it is returning me a varchar2 which contains xml tags.I want to pull values for each element. any one can provide me procedure/function to pull data from it.
Below is the output from webservice.

<ResponseEnvelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.newcorp.com/messaging"> <ResponseHeader xmlns="http://www.newcorp.com/messaging" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RequestId>234346</RequestId> <ResponseId>136246</ResponseId> <MessageVersion>1.01</MessageVersion> <RequestTimestamp>2011-05-20T15:17:41.073Z</RequestTimestamp> <ResponseTimestamp>2011-05-20T15:17:41.550Z</ResponseTimestamp> <SenderId>contactcenter</SenderId> <ProgramName></ProgramName> <TestProdFlag>contactcenter</TestProdFlag> <ResultCode>0</ResultCode> <Errors> </Errors> </ResponseHeader> <ResponseBody> <InFlightAmount>0.0</InFlightAmount> <PaidAmount>0.0</PaidAmount> <ThresholdAmount>300</ThresholdAmount> <AnnualCAP>6000.0</AnnualCAP> <BalanceAmount>5700.0</BalanceAmount> <ClaimEligible>Y</ClaimEligible> <ServiceType>In-Home</ServiceType> </ResponseBody> </ResponseEnvelope>

ResponseId, MessageVersion etc are element name. the value between element names are to be retrived.

Pls. help

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508942 is a reply to message #508940]

Wed, 25 May 2011 06:18

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

Senior Member
Account Moderator

There are examples in our "JDeveloper, Java & XML" forum where I move your topic.
Search for "xmlsequence" or "extractvalue" and you will get many.

Regards
Michel

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508945 is a reply to message #508942]

Wed, 25 May 2011 06:26

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

ind> select html.TO_TEXT('<ResponseEnvelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.newcorp.com/messaging"> <ResponseHeader
 xmlns="http://www.newcorp.com/messaging" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="ht
tp://www.w3.org/2001/XMLSchema-instance"> <RequestId>234346</RequestId> <ResponseId>136246</Response
Id> <MessageVersion>1.01</MessageVersion> <RequestTimestamp>2011-05-20T15:17:41.073Z</RequestTimesta
mp> <ResponseTimestamp>2011-05-20T15:17:41.550Z</ResponseTimestamp> <SenderId>contactcenter</SenderI
d> <ProgramName></ProgramName> <TestProdFlag>contactcenter</TestProdFlag> <ResultCode>0</ResultCode>
 <Errors> </Errors> </ResponseHeader> <ResponseBody> <InFlightAmount>0.0</InFlightAmount> <PaidAmoun
t>0.0</PaidAmount> <ThresholdAmount>300</ThresholdAmount> <AnnualCAP>6000.0</AnnualCAP> <BalanceAmou
nt>5700.0</BalanceAmount> <ClaimEligible>Y</ClaimEligible> <ServiceType>In-Home</ServiceType> </Resp
onseBody> </ResponseEnvelope>') from dual
  2  /

HTML.TO_TEXT('<RESPONSEENVELOPEXMLNS:XSD="HTTP://WWW.W3.ORG/2001/XMLSCHEMA"XMLNS:XSI="HTTP://WWW.W3.
----------------------------------------------------------------------------------------------------

234346  136246  1.01  2011-05-20T15:17:41.073Z  2011-05-20T15:17:41.550Z  contactcenter    contactcente
.0  300  6000.0  5700.0  Y  In-Home


1 row selected.

ind>  select HTML_TAG_REMOVE('<ResponseEnvelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:x
si="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.newcorp.com/messaging"> <ResponseHe
ader xmlns="http://www.newcorp.com/messaging" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi
="http://www.w3.org/2001/XMLSchema-instance"> <RequestId>234346</RequestId> <ResponseId>136246</Resp
onseId> <MessageVersion>1.01</MessageVersion> <RequestTimestamp>2011-05-20T15:17:41.073Z</RequestTim
estamp> <ResponseTimestamp>2011-05-20T15:17:41.550Z</ResponseTimestamp> <SenderId>contactcenter</Sen
derId> <ProgramName></ProgramName> <TestProdFlag>contactcenter</TestProdFlag> <ResultCode>0</ResultC
ode> <Errors> </Errors> </ResponseHeader> <ResponseBody> <InFlightAmount>0.0</InFlightAmount> <PaidA
mount>0.0</PaidAmount> <ThresholdAmount>300</ThresholdAmount> <AnnualCAP>6000.0</AnnualCAP> <Balance
Amount>5700.0</BalanceAmount> <ClaimEligible>Y</ClaimEligible> <ServiceType>In-Home</ServiceType> </
ResponseBody> </ResponseEnvelope>') from dual;

HTML_TAG_REMOVE('<RESPONSEENVELOPEXMLNS:XSD="HTTP://WWW.W3.ORG/2001/XMLSCHEMA"XMLNS:XSI="HTTP://WWW.
----------------------------------------------------------------------------------------------------
  234346 136246 1.01 2011-05-20T15:17:41.073Z 2011-05-20T15:17:41.550Z contactcenter  contactcenter 
00.0 5700.0 Y In-Home


1 row selected.

IS the above you are looking at ?

If so then you can look at asktom strip_html or

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:828426949078

function str_html ( line in varchar2 ) return varchar2 is
    x       varchar2(32767) := null;
    in_html boolean         := FALSE;
    s       varchar2(1);
  begin
    if line is null then
      return line;
    end if;
    for i in 1 .. length( line ) loop
      s := substr( line, i, 1 );
      if in_html then
        if s = '>' then
          in_html := FALSE;
        end if;
      else
        if s = '<' then
          in_html := TRUE;
        end if;
      end if;
      if not in_html and s != '>' then
        x := x || s;
      end if;
    end loop;
    return x;
  end str_html;

http://www.orafaq.com/forum/mv/msg/153387/436834/0/#msg_436834

http://www.orafaq.com/forum/mv/msg/153387/436395/0/#msg_436395

Sriram

[Updated on: Wed, 25 May 2011 06:33]

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508959 is a reply to message #508945]

Wed, 25 May 2011 06:55

sangakirankumar
Messages: 18
Registered: August 2008
Location: india

Junior Member

Hi,
This is not working for me.
Also i should not add or modify any java clases. I should only use pl/sql to retrieve the data.

Thanks Sriram for your reply.

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508962 is a reply to message #508959]

Wed, 25 May 2011 07:00

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

Senior Member
Account Moderator

Quote:

Also i should not add or modify any java clases. I should only use pl/sql to retrieve the data.

Did you search as I suggested?

Regards
Michel

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508963 is a reply to message #508959]

Wed, 25 May 2011 07:00

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Given links have solutions based on sql /Plsql.
Check those links again ...
Moreover .... "function str_html" will work.Is n`t that a pl/sql ?

Sriram

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508969 is a reply to message #508963]

Wed, 25 May 2011 07:29

sangakirankumar
Messages: 18
Registered: August 2008
Location: india

Junior Member

Sriram,

str_html is working fine, thanks for it. But the result am getting is:

, , ,234346, ,136246, ,1.01, ,2011-05-20T15:17:41.073Z, ,2011-05-20T15:17:41.550Z, ,contactcenter, ,, ,contactcenter, ,0, , , , , ,0.0, ,0.0, ,300, ,6000.0, ,5700.0,,Y,,In-Home,,,

How can i know for what element value what value is there.
Example: if I want to know element value of ClaimEligible then ???
Actually If I get only ClaimEligible value then I will be happy.
Really thanks for your immediate response.

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508970 is a reply to message #508969]

Wed, 25 May 2011 07:32

sangakirankumar
Messages: 18
Registered: August 2008
Location: india

Junior Member

Michel,

Really thanks for moving my question into this forum, it is really very helpful for me.
I tried using xmlsequence and extractvalue but it is returning null.

I think because of below data it is returning null.

xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.newcorp.com/messaging"

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508971 is a reply to message #508970]

Wed, 25 May 2011 07:35

sangakirankumar
Messages: 18
Registered: August 2008
Location: india

Junior Member

Michel,

To explain more.

I just ran below 2 select statements 1st one will return null, where as second one will return Y.

1.
SELECT extractvalue(VALUE(x), '/ClaimEligible')
FROM TABLE(xmlsequence(EXTRACT(xmltype('<ResponseEnvelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.newcorp.com/messaging"><ClaimEligible>Y</ClaimEligible><ServiceType>In-Home</ServiceType></ResponseEnvelope>'), '/ResponseEnvelope/ClaimEligible'))) x

2.
SELECT extractvalue(VALUE(x), '/ClaimEligible')
FROM TABLE(xmlsequence(EXTRACT(xmltype('<ResponseEnvelope><ClaimEligible>Y</ClaimEligible><ServiceType>In-Home</ServiceType></ResponseEnvelope >'), '/ResponseEnvelope/ClaimEligible'))) x

Thanks,
Kiran

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508974 is a reply to message #508971]

Wed, 25 May 2011 07:39

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

Senior Member
Account Moderator

extractvalue should also contains the namespace in the first one.
For instance:
http://www.orafaq.com/forum/mv/msg/164279/486863/102589/#msg_486863

Regards
Michel

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #508988 is a reply to message #508974]

Wed, 25 May 2011 08:41

sangakirankumar
Messages: 18
Registered: August 2008
Location: india

Junior Member

Michel,

I tried a lot but it is returning null still.

can you pls. correct me where am wrong.

SELECT extractvalue(VALUE(x), '/ClaimEligible','xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.newcorp.com/messaging"')
FROM TABLE(xmlsequence(EXTRACT(xmltype('<ResponseEnvelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.newcorp.com/messaging">
<ClaimEligible>Y</ClaimEligible>
<ServiceType>In-Home</ServiceType>
</ResponseEnvelope>'), '/ResponseEnvelope/ClaimEligible'))) x

Report message to a moderator

Re: Need a Procedure for reading xml element value [message #509001 is a reply to message #508988]

Wed, 25 May 2011 09:17

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

One way

SELECT EXTRACTVALUE (COLUMN_VALUE,
                              '//MessageVersion',
                              'xmlns="http://www.newcorp.com/messaging"')
                   AS MessageVersion,
            EXTRACTVALUE (COLUMN_VALUE,
                              '//ClaimEligible',
                              'xmlns="http://www.newcorp.com/messaging"')
                   AS ClaimEligible,
                EXTRACTVALUE (COLUMN_VALUE,
                              '//ServiceType',
                              'xmlns="http://www.newcorp.com/messaging"')
                   AS ServiceType
           FROM TABLE(XMLSEQUENCE(EXTRACT (
                                     XMLTYPE ((
  q'#<ResponseEnvelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.newcorp.com/messaging">
  <ResponseHeader xmlns="http://www.newcorp.com/messaging" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RequestId>234346</RequestId>
    <ResponseId>136246</ResponseId>
    <MessageVersion>1.01</MessageVersion>
    <RequestTimestamp>2011-05-20T15:17:41.073Z</RequestTimestamp>
    <ResponseTimestamp>2011-05-20T15:17:41.550Z</ResponseTimestamp>
    <SenderId>contactcenter</SenderId>
    <ProgramName/>
    <TestProdFlag>contactcenter</TestProdFlag>
    <ResultCode>0</ResultCode>
    <Errors/>
  </ResponseHeader>
  <ResponseBody>
    <InFlightAmount>0.0</InFlightAmount>
    <PaidAmount>0.0</PaidAmount>
    <ThresholdAmount>300</ThresholdAmount>
    <AnnualCAP>6000.0</AnnualCAP>
    <BalanceAmount>5700.0</BalanceAmount>
    <ClaimEligible>Y</ClaimEligible>
    <ServiceType>In-Home</ServiceType>
  </ResponseBody>
</ResponseEnvelope>#')),'//ResponseEnvelope',                                     'xmlns="http://www.newcorp.com/messaging"')));

Report message to a moderator