On Oracle 11gR2 I need to retrieve portions of an XML file and create a new one:
SELECT XMLQUERY
('for $e in /edi_l/trader
where $e//pod="XX999"
order by $e
return <pod>{$e}</pod>'
passing MY_T.XML_FILE
returning content
)
FROM MYTABLE MY_T;
The output:
<pod><trader cdisp="AB1111">
<idimpp num="1234">
<hdr>
<odn>567</odn>
<pod>XX999</pod>
</hdr>
...
</idimpp>
</trader>
</pod>
If I omit the tag
<pod></pod>
SELECT XMLQUERY
('for $e in /edi_l/trader
where $e//pod="XX999"
order by $e
return {$e}
passing MY_T.XML_FILE
returning content
)
FROM MYTABLE MY_T;
I get errors:
ORA-19114: XPST0003
LPX-00801: XQuery syntax error at '{'
4 return {$e}
- ^
19114. 00000 - "XPST0003 - error during parsing the XQuery expression: %s"
*Cause: An error occurred during the parsing of the XQuery expression.
*Action: Check the detailed error message for the possible causes.
Error at Line: 30 Column: 6
Why?
I need to retrieve the data without creating a new external tag.