title: PL/SQL Using XMLQuery/XQuery to insert elements from one XML document inside another at a specific point
body: <p>I was wondering if anyone could help please? </p>
  
  <p>I am trying to insert all elements (below root level) from one xml document into another. Both documents reside in an Oracle 12c table as XMLType columns. I want to be able to read them in from their table, perform the transformation and then write it out as a new document to the table. Most of this is straightforward but I am hitting a problem with the XQuery operation doing the insertion... I naively imagined this would be simple but can't seem to get it right. Until Oracle 12c I was able to use the function appendchildxml thus:</p>
  
  <pre><code>select
  appendchildxml(my_output, '/Message', my_other_file, 'xmlns="SFA/ILR/2016-17"') into my_output
  from
  dual;
  </code></pre>
  
  <p>but that function is deprecated in Oracle 12c, so I need to use XQuery now.</p>
  
  <pre><code>update xml_testing_12c
  set 
  my_ilrxml = 
  XMLQuery('
  xquery version "1.0";
  
  copy $ilr := .
  modify
      insert nodes $dp_out/Destinations/node() as last into $ilr/Message
  return $ilr
  '
  passing my_ilr, my_dpoutcome as "dp_out" returning content
  )
  where
  run_number = (select max(run_number) from xml_testing_12c);
  </code></pre>
  
  <p>my_ilr and my_dpoutcome are PL/SQL XMLType variables containing my valid XML documents. They have the following structure:</p>
  
  <p>my_ilr is as follows:</p>
  
  <pre><code>&lt;Message xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="SFA/ILR/2016-17" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xsi:schemaLocation="SFA/ILR/2016-17"&gt;
    &lt;Header&gt;
    &lt;CollectionDetails&gt;
     &lt;/CollectionDetails&gt;
    &lt;Source&gt;
     &lt;/Source&gt;
    &lt;/Header&gt;
    &lt;LearningProvider&gt;
     &lt;/LearningProvider&gt;
    &lt;Learner&gt;
      &lt;LearningDelivery&gt;
       &lt;/LearningDelivery&gt;
    &lt;/Learner&gt;
  &lt;/Message&gt;
  </code></pre>
  
  <p>my_dpoutcome is as follows:</p>
  
  <pre><code>&lt;Destinations&gt;
    &lt;LearnerDestinationandProgression&gt;
      &lt;LearnRefNumber&gt;&lt;/LearnRefNumber&gt;
      &lt;ULN&gt;&lt;/ULN&gt;
      &lt;DPOutcome&gt;
        &lt;OutType&gt;&lt;/OutType&gt;
        &lt;OutCode&gt;&lt;/OutCode&gt;
        &lt;OutStartDate&gt;&lt;/OutStartDate&gt;
        &lt;OutCollDate&gt;&lt;/OutCollDate&gt;
      &lt;/DPOutcome&gt;
    &lt;/LearnerDestinationandProgression&gt;
  &lt;/Destinations&gt;
  </code></pre>
  
  <p>There are a large number of LearnerDestinationandProgression elements within the parent node.</p>
  
  <p>I am getting the following error:</p>
  
  <p>ORA-19112: error raised during evaluation: 
  XVM-01155: [XUDY0027] Invalid target expression
  ORA-06512: at line 286</p>
  
  <p>Any help would be much appreciated.</p>
  
  <p>Thanks.</p>