I have a simple query:
WITH xtbl AS (
SELECT XMLTYPE ('<a><b>1</b></a>') AS xcol
FROM DUAL
)
SELECT XMLQUERY ('copy $tmp := .
modify
insert node <c>2</c>
into $tmp/a/b
return $tmp'
PASSING xcol
RETURNING CONTENT) AS newxcol
FROM xtbl;
What I'm trying to do is to insert a node <c>
after <b>
inside the node <a>
but Oracle 12c is throwing this error:
ORA-19114: XPST0003 - error during parsing the XQuery expression:
LPX-00801: XQuery syntax error at 'EOF'
5 -
- ^
I want the output as:
NEWXCOL
-------------
<a>
<b>1</b>
<c>2</c/>
</a>
I have tried looking in Oracle Docs for a replacement of appendChildXML
and follow that example but only got the error.
I know it is very basic and I'm missing something obvious. Please help.
Following code should be working for 11 and 12 (deprecated):
SELECT insertXMLafter(XMLType('<a><b>1</b><c>3</c></a>'),
'/a/b', XmlType('<c>2</c>'))
FROM dual;
Same code using new XMLQuery syntax:
SELECT XMLQuery('copy $tmp := . modify insert node
<c>2</c>
after $tmp/a/b
return $tmp'
PASSING XmlType('<a><b>1</b><c>3</c></a>') RETURNING CONTENT)
FROM dual;
More details regarding XMLQuery and also the old deprecated functions can be found here: http://docs.oracle.com/database/121/ADXDB/app_depr_upd.htm#ADXDB6160