sql xml oracle-database xml-namespaces oracle11gr2

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?

SELECT *
  FROM XMLTABLE (
         XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
                       'http://www.w3.org/2001/XMLSchema' as "xsd",
                       'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
                       'http://service.xxx.com/' AS "xxx"),
                       '/soapenv:Envelope/soapenv:Body/xxx:sendDataResponse/xxx:sendDataReturn/xxx:result'
         PASSING XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>' ||
                         '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" ' ||
                         '                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" ' ||
                         '                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' ||
                         '  <soapenv:Body>' ||
                         '    <sendDataResponse xmlns="http://service.xxx.com">' ||
                         '      <sendDataReturn>' ||
                         '        <result>' ||
                         '          <build>Build 1</build>' ||
                         '          <status>SUCCESS</status>' ||
                         '          <cntr_statuses>' ||
                         '            <cntr_result>' ||
                         '              <cntr>1234567890A</cntr><cntr_status>SUCCESS</cntr_status>' ||
                         '            </cntr_result>' ||
                         '            <cntr_result>' ||
                         '              <cntr>1234567890B</cntr><cntr_status>SUCCESS</cntr_status>' ||
                         '            </cntr_result>' ||
                         '          </cntr_statuses>' ||
                         '        </result>' ||
                         '      </sendDataReturn>' ||
                         '    </sendDataResponse>' ||
                         '  </soapenv:Body>' ||
                         '</soapenv:Envelope>')
         COLUMNS status VARCHAR2(20) PATH 'xxx:status')  xmlstuff ;

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!

Solution

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:

SELECT *
FROM XMLTABLE (
       XMLNAMESPACES(
         'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
         'http://www.w3.org/2001/XMLSchema' as "xsd",
         'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
         DEFAULT 'http://service.xxx.com'
       ),
       '/soapenv:Envelope/soapenv:Body/sendDataResponse/sendDataReturn/result'
       PASSING XMLTYPE(
         '<?xml version="1.0" encoding="UTF-8"?>' ||
         '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" ' ||
         '                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" ' ||
         '                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' ||
         '  <soapenv:Body>' ||
         '    <sendDataResponse xmlns="http://service.xxx.com">' ||
         '      <sendDataReturn>' ||
         '        <result>' ||
         '          <build>Build 1</build>' ||
         '          <status>SUCCESS</status>' ||
         '          <cntr_statuses>' ||
         '            <cntr_result>' ||
         '              <cntr>1234567890A</cntr><cntr_status>SUCCESS</cntr_status>' ||
         '              <cntr>1234567890B</cntr><cntr_status>SUCCESS</cntr_status>' ||
         '            </cntr_result>' ||
         '          </cntr_statuses>' ||
         '        </result>' ||
         '      </sendDataReturn>' ||
         '    </sendDataResponse>' ||
         '  </soapenv:Body>' ||
         '</soapenv:Envelope>'
)

sqlfiddle here

Then to get the first cntr and cntr_status:

SELECT *
FROM XMLTABLE (
       XMLNAMESPACES(
         'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
         'http://www.w3.org/2001/XMLSchema' as "xsd",
         'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
         DEFAULT 'http://service.xxx.com'
       ),
       '/soapenv:Envelope/soapenv:Body/sendDataResponse/sendDataReturn/result'
       PASSING XMLTYPE(
         '<?xml version="1.0" encoding="UTF-8"?>' ||
         '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" ' ||
         '                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" ' ||
         '                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' ||
         '  <soapenv:Body>' ||
         '    <sendDataResponse xmlns="http://service.xxx.com">' ||
         '      <sendDataReturn>' ||
         '        <result>' ||
         '          <build>Build 1</build>' ||
         '          <status>SUCCESS</status>' ||
         '          <cntr_statuses>' ||
         '            <cntr_result>' ||
         '              <cntr>1234567890A</cntr><cntr_status>SUCCESS</cntr_status>' ||
         '              <cntr>1234567890B</cntr><cntr_status>SUCCESS</cntr_status>' ||
         '            </cntr_result>' ||
         '          </cntr_statuses>' ||
         '        </result>' ||
         '      </sendDataReturn>' ||
         '    </sendDataResponse>' ||
         '  </soapenv:Body>' ||
         '</soapenv:Envelope>'
)
  COLUMNS
    status      VARCHAR2(20) PATH 'status',
    cntr        VARCHAR2(20) PATH 'cntr_statuses/cntr_result/cntr[1]',
    cntr_status VARCHAR2(20) PATH 'cntr_statuses/cntr_result/cntr_status[1]'
)  xmlstuff;

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.

SELECT x.*
FROM   table_name t
       CROSS JOIN
       XMLTABLE(
         XMLNAMESPACES(
           'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
           'http://www.w3.org/2001/XMLSchema' as "xsd",
           'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
           DEFAULT 'http://service.xxx.com'
         ),
         '/soapenv:Envelope/soapenv:Body/sendDataResponse/sendDataReturn/result/cntr_statuses/cntr_result'
         PASSING XMLTYPE(t.xml)
         COLUMNS
           status      VARCHAR2(20) PATH './../../status',
           cntr        VARCHAR2(20)  PATH 'cntr',
           cntr_status VARCHAR2(20)  PATH 'cntr_status'
       ) x;

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:

SELECT x.status,
       c.cntr,
       c.cntr_status
FROM   table_name t
       CROSS JOIN
       XMLTABLE(
         XMLNAMESPACES(
           'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
           'http://www.w3.org/2001/XMLSchema' as "xsd",
           'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
           DEFAULT 'http://service.xxx.com'
         ),
         '/soapenv:Envelope/soapenv:Body/sendDataResponse/sendDataReturn/result'
         PASSING XMLTYPE(t.xml)
         COLUMNS
           status        VARCHAR2(20) PATH 'status',
           cntr_statuses XMLTYPE      PATH 'cntr_statuses'
       ) x
       CROSS JOIN
       XMLTABLE(
         XMLNAMESPACES(
           'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
           'http://www.w3.org/2001/XMLSchema' as "xsd",
           'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
           DEFAULT 'http://service.xxx.com'
         ),
         '/cntr_statuses/cntr_result'
         PASSING x.cntr_statuses
         COLUMNS
           cntr        VARCHAR2(20) PATH 'cntr',
           cntr_status VARCHAR2(20) PATH 'cntr_status'
       ) c;

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

STATUS	CNTR	CNTR_STATUS
SUCCESS	1234567890A	SUCCESS
SUCCESS	1234567890B	SUCCESS

sqlfiddle here