Using ora:view with XMLTable to Query a Relational Table as XML

Question

I have an Oracle relational table called DOCTYPES with columns ID, DOCTYPE, SUBTYPE.

When I run the following statement in Oracle SQL Developer

SELECT * FROM XMLTable('for $i in ora:view("LAZ", "DOCTYPES")/ROW
                        return $i/SUBTYPE')

I get back the results between tags as expected. But when I run the following statement I get an error:

SELECT * FROM XMLTable('for $i in ora:view("LAZ", "DOCTYPES")/ROW
                        return <SUBTYPE="{$i/SUBTYPE}"/>')

LPX-00801: XQuery syntax error at '='. I don't understand why the second statement doesn't work.

Thank you very much for your help in advance.

Answer 1

Don't know what Oracle makes of XQuery, but the statement between the apostrophes clearly has an XQuery syntax error at '=', as IMO was correctly diagnosed.

This is because you are opening a direct element constructor, but an equals sign incorrectly follows the tag name. An equals sign is used inside of a direct element constructor to separate attribute names from attribute values. So the following might work:

SELECT * FROM XMLTable('for $i in ora:view("LAZ", "DOCTYPES")/ROW
                        return <SUBTYPE name="{$i/SUBTYPE}"/>')

For the specification, please refer to 3.7.1 Direct Element Constructors in the XQuery recommendation.