XMLTable query returns no result

Question

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!

Do you have any control over the XML format and could change it so that each `cntr`/`cntr_status` pair is wrapped in different `cntr_result` elements? Trying to get each `cntr` element and then the following sibling is a pain and it would be much simpler if there was only a singleton of each in the wrapping element. — MT0, Jul 29 '21 at 01:04
Yes, sorry, my bad. I have corrected the example. That is indeed how the result comes back to me. — DCookie, Jul 29 '21 at 12:47

MT0 · Accepted Answer · 2021-07-29T13:14:06.857

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

sqlfiddle here

Thank you very much! I know I tried various incantations with DEFAULT but none of them worked. You sir/madam, are my hero today! — DCookie, Jul 29 '21 at 12:57
@DCookie Updated with a simpler version which may or may not work depending on whether the minor version of your Oracle is high enough to not have the bug. — MT0, Jul 29 '21 at 13:15
thanks, the simplified query you show works very well for my application. I've now got a PL/SQL package that generates the request, sends it, and processes the responses. We're at 11.2.0.4. Thanks much again! — DCookie, Jul 29 '21 at 21:44

XMLTable query returns no result

1 Answers1

Update for revised XML format