According to the documentation of the function XMLQUERY, RETURNING CONTENT is the only valid option and in the documentation it explicitly states (on docs.oracle.com/en/database/oracle/oracle-database/18/adxdb/xquery-and-XML-DB.html):
"Oracle XML DB supports only the
RETURNING CONTENT clause of SQL/XML function
XMLQuery; it does not support the
RETURNING SEQUENCE clause."
On https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/XMLQUERY.html RETURNING SEQUENCE is not mentioned, but the syntax does not offer any other option than RETURNING CONTENT.
Even the syntax check in SQL Delevoper considers SEQUENCE to be incorrect after RETURNING. And yet it appears to be working correctly. If I use any other random word after RETURNING then I get a syntax error on runtime (ORA-19101: CONTENT keyword expected), but SEQUENCE works and seems to behave correctly (does not add a document node around the result).
So the question is, has RETURNING SEQUENCE been implemented and is supported, but someone forgot to update the documentation (and maybe include some info in the "What's new" section)? Or is this an intentional secret because this feature is not tested and shouldn't have been included in the released version?
Here is a simple example that shows that RETURNING SEQUENCE works:
SELECT XMLQUERY('$x/name()' PASSING XMLQUERY('<R>5</R>' RETURNING SEQUENCE) AS "x" RETURNING CONTENT)
Returns nicely "R" (as XMLTYPE). $x is the R element node.
SELECT XMLQUERY('$x/name()' PASSING XMLQUERY('<R>5</R>' RETURNING CONTENT) AS "x" RETURNING CONTENT)
Returns null since the document node added by RETURNING CONTENT does not have a name. $x in a document node.
And to confirm that the R element node is still there:
SELECT XMLQUERY('$x/*/name()' PASSING XMLQUERY('<R>5</R>' RETURNING CONTENT) AS "x" RETURNING CONTENT)
Which returns "R". $x is the document node, $x/* matches the R element node.