Is there a workaround to use XMLTABLE function while passing a subquery column?

Question

I'm trying to process the result of a virtual function into XMLTABLE in order to generate a more comprehensible view of the data that I'm receiving.

I tried to use the next script:

SELECT XTABLE.* FROM
(SELECT SOAP_BODY FROM "ABASTECIMIENTO_DEV"."INICIATIVA_310_MBTU_invoke"('<SOAP_PARAMETERS>')) SOAP,
XMLTABLE (
    '/doc/item' PASSING SOAP.SOAP_BODY
    COLUMNS
    "id" NVARCHAR(10) path 'id'
) XTABLE;

What I expected to happen was that it should return a table with only one column called "id". Instead I got the next error:

Error: (dberror) 259 - invalid table name: table does not exist > SOAP: line 4 col 24 (at pos 409)

Is there a possible way to pass the result of the subquery into the xmltable?

Answer 1

The error is telling you that it is looking for a table called SOAP on line 4

If you look at line 4, sure enough there is a reference to SOAP.SOAP_BODY

The first part of that (before the period) is a table name or table alias.

So, you need to upwards in the code to find where you have either used a table called "SOAP" or that you have created a table alias SOAP - there is no table called "SOAP" and no alias "SOAP"

So as you aren't needing an actual table called "SOAP" you need to declare a table alias instead.

SELECT XTABLE.* 
FROM (
   SELECT SOAP_BODY 
   FROM "ABASTECIMIENTO_DEV"."INICIATIVA_310_MBTU_invoke"('<SOAP_PARAMETERS>')) AS SOAP -- alias
   , XMLTABLE (
    '/doc/item' PASSING SOAP.SOAP_BODY  -- refers to alias
    COLUMNS
    "id" NVARCHAR(10) path 'id'
    ) AS XTABLE

If you are still not making progress run this:

SELECT SOAP_BODY FROM "ABASTECIMIENTO_DEV"."INICIATIVA_310_MBTU_invoke"('<SOAP_PARAMETERS>');

To ascertain if you are receiving valid XML.