Skip to content
Advertisement

XMLTable query returns no result

I’m only passingly familiar with XML. I need to parse a response from a SOAP request. From a lot of searching, I’ve developed the following query to try to extract the status. Ultimately, I’d like to get the status, cntr and cntr_status fields from the response. My query gives no error, but also no results. What noob error am I making?

A sample response from the service is hard-coded into the XMLTYPE function.

I’ve tried any number of query strings and column paths involving the xxx namespace, all yielding no results.

There could be hundreds of cntr and cntr_status pairs.

Thanks for looking!

Advertisement

Answer

Using the DEFAULT namespace (since you don’t define a prefix for http://service.xxx.com) and removing the references to xxx: appears to work:

sqlfiddle here


Then to get the first cntr and cntr_status:

sqlfiddle here


Update for revised XML format

Ideally, you should be able to use the XPATH '/soapenv:Envelope/soapenv:Body/sendDataResponse/sendDataReturn/result/cntr_status/cntr_result' in the XMLTABLE and then get the status with the path ./../../status; however, I keep getting null values when trying to traverse to a parent element and couldn’t find a working solution.

sqlfiddle here

According to this comment, it will work in Oracle 11.2.0.4 but if you try it in Oracle 11.2.0.2 then status will be NULL (which is the result seen on SQLFiddle).


Instead, with multiple cntr_result elements you can use two XMLTABLE:

Assuming your data is in the xml column of the table_name table.

Then the output is:

STATUS CNTR CNTR_STATUS
SUCCESS 1234567890A SUCCESS
SUCCESS 1234567890B SUCCESS

sqlfiddle here

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement