Home » SQL & PL/SQL » SQL & PL/SQL » Extracting the data from web service response (R12)
Extracting the data from web service response [message #580622] Tue, 26 March 2013 05:02 Go to next message
suman.g
Messages: 88
Registered: June 2009
Member
Hi Experts,

I need help on extracting tag values from REST web service response.

Webservice response

Quote:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <LicenseDetailsRes xmlns="com:*******services:LicenseDetailsRes">
<ResponseCode>LD001 - SUCCESS</ResponseCode>
<OrderNumber>51663852</OrderNumber>
<LineNumber>8</LineNumber>
<quantity>0</quantity>
</LicenseDetailsRes>


I am storing above response in a table column 'output_xm' of xmltype datatype.

My requirement is to extract 'ResponseCode' & 'quantity' values from above response.
For which I am using below query but it's not working.

SELECT x.order_number
,x.line_number
,x.tnc_pid
,EXTRACT (VALUE (d), 'LicenseDetailsRes/ResponseCode/text()').getstringval () status
,EXTRACT (VALUE (d), 'LicenseDetailsRes/quantity/text()').getstringval () pak_quantity
FROM xxcfi.xxcfi_vt_tnc_pak_details x
,TABLE (XMLSEQUENCE (EXTRACT (x.output_xml, '/LicenseDetailsRes'))) d
WHERE x.order_number = 50403901
AND x.line_number = 2
AND x.tnc_pid = 'R-LMS-4.0-100-K9';


Please help.

Thanks,
Suman
Re: Extracting the data from web service response [message #580634 is a reply to message #580622] Tue, 26 March 2013 06:00 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Try to use the namespace in EXTRACT:
SELECT x.order_number
,x.line_number
,x.tnc_pid
,EXTRACT (VALUE (d), 'LicenseDetailsRes/ResponseCode/text()').getstringval () status
,EXTRACT (VALUE (d), 'LicenseDetailsRes/quantity/text()').getstringval () pak_quantity
FROM xxcfi.xxcfi_vt_tnc_pak_details x
,TABLE (XMLSEQUENCE (EXTRACT (x.output_xml, '/LicenseDetailsRes','xmlns="com:*******services:LicenseDetailsRes"'))) d
WHERE x.order_number = 50403901
AND x.line_number = 2
AND x.tnc_pid = 'R-LMS-4.0-100-K9';
icon14.gif  Re: Extracting the data from web service response [message #580636 is a reply to message #580634] Tue, 26 March 2013 06:35 Go to previous message
suman.g
Messages: 88
Registered: June 2009
Member
Thanks a lot Jum and appreciate your quick response .

With your suggestion, I am able to derive required values.

Cheers,
Suman

[Updated on: Tue, 26 March 2013 06:35]

Report message to a moderator

Previous Topic: multiple rows based on one column
Next Topic: procedure to display all the rows from a table
Goto Forum:
  


Current Time: Thu Oct 23 16:08:45 CDT 2014

Total time taken to generate the page: 0.20229 seconds