Inserting a child node in an XMLTYPE column

Question

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.

Finnougrian

umm, are your sure you are using at least Oracle Database 12c Release 1? Your error message quite clearly states that XQuery Update is not supported. Based on the documentation you linked to it was introduced with the above version. So, what version of Oracle are you using? – Caisson

@dirkk, I'm using Oracle 12.1.0.1. I have updated the error that I'm getting. – Finnougrian

Your query is fine. You simply forgot closing tag for the node being added (<c>2</c>). Moreover, to get the desired result, the target path needs to be $tmp/a not the $tmp/a/b. – Fichu

@NicholasKrasnov: That was very helpful, thanks! I corrected the query and executed it on Oracle versions 11.2.0.1.0, 11.2.0.4.0 and 12.1.0.1.0. I get an error on the first version but the query executes as expected on the latter two. I need a solution that will work on all three. I have also corrected the query. – Finnougrian

@Finnougrian well, XQUF was added to Oracle in 11.2.0.3.0, so it you can't use it for previous versions. If you need one single solutions for all versions, you will have to use the old-fashioned XML update methods provided by Oracle, which are now deprecated (but still usable). – Caisson

Answer 1

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

Towns

Hi! I knew someone would post this as an answer but I believe a bounty should be awarded only if it adds significant value to the question and the comments. Your answer is definitely right but I would award manual bounty only if you go beyond the comments and provide further explanation. – Finnougrian

I added the same code using XMLQuery and the link to the Oracle documentation which has some nice examples on how to convert the oracle syntax to XMLQuery. I don't think there is much more to add in order to solve this question. – Towns

Works fine on all the three versions I have mentioned. +50. – Finnougrian

Answer 2

One ugly but possible way to use only one SQL query that works correctly on older and current database versions would be to use the CASE method in SQL:

WITH tbl AS
 (SELECT SUBSTR(MAX(version),1,2) as ver, XMLType('<a><b>1</b><c>3</c></a>') as xcol
    FROM v$instance)
SELECT 
   CASE WHEN ver < '12' THEN
      insertXMLafter(xcol,
                 '/a/b', XmlType('<c>2</c>'))
   ELSE
      XMLQuery('copy $tmp := . modify insert node 
                 <c>2</c>
                 after $tmp/a/b 
                 return $tmp'
                PASSING xcol RETURNING CONTENT) 
   END
FROM tbl;

Of course that won't help you if the insertXMLafter method gets removed in future releases of the database. That would produce an invalid SQL. But for now you would be good and use the right method on the right versions.

Tremml

well, what's the point if it gets invalid with a newer version? insertXMLafter is deprecated, but it is not wrong nor is using XQuery Update right. It is the more standard-compliant and better in this regard that one is more vendor-independent, but this does not make it right. I do not see any added value in using such a solution if you have the restriction of having to use the same query for all different versions. – Caisson

Well, it may or may not get invalid in future versions of the database if Oracle decides to de-support and therefore remove insertXMLafter altogether. Ideally you would handle different calls to different versions within the code itself however, Rachcha asked for something that would work on both 11g and 12c. So I assume that the former is not an option in this case. – Tremml

Yes, but if it gets invalid if insertXMLafter gets removed, why not simply use that, without the CASE? Either way, it would be invalid in future versions without this functionality. – Caisson

Yes, you're right. You could just use insertXMLafter if that fulfills your requirements. This is just a simple wrapper for using XQuery Update based on the question above and insertXMLafter for older versions where you don't have XQuery Update available yet. If you want to avoid the SQL from failing in future releases you could also create a PL/SQL function and use Conditional Compilation in order to use the right method in the right version transparently. – Tremml

Thanks for your answer, but it doesn't work on 11.2.0.1. Gives the same error. And it also doesn't work if your user account doesn't have access to v$instance view. – Finnougrian

You're right, just tested it on 11.2.0.1 and XMLQuery throws an update, although the expression shouldn't be evaluated. When called via a function however it runs fine. Not sure why it's evaluated directly although it's in the ELSE part. I'll try to find out more and give an update on it. – Tremml

Hi, took a while but I finally got behind why the query is failing. It is actually failing during the parse of the query. During parsing Oracle applys it's Query Rewrite functionality to potentially rewrite the query into a better performing one. Oracle also does that for the XPath within the XQuery. It is the XPath rewrite check that actually detects that the XQuery uses XQuery Update and throws an error. – Tremml