PL/SQL Using XMLQuery/XQuery to insert elements from one XML document inside another at a specific point

Question

I was wondering if anyone could help please?

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:

select
appendchildxml(my_output, '/Message', my_other_file, 'xmlns="SFA/ILR/2016-17"') into my_output
from
dual;

but that function is deprecated in Oracle 12c, so I need to use XQuery now.

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);

my_ilr and my_dpoutcome are PL/SQL XMLType variables containing my valid XML documents. They have the following structure:

my_ilr is as follows:

<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">
  <Header>
  <CollectionDetails>
   </CollectionDetails>
  <Source>
   </Source>
  </Header>
  <LearningProvider>
   </LearningProvider>
  <Learner>
    <LearningDelivery>
     </LearningDelivery>
  </Learner>
</Message>

my_dpoutcome is as follows:

<Destinations>
  <LearnerDestinationandProgression>
    <LearnRefNumber></LearnRefNumber>
    <ULN></ULN>
    <DPOutcome>
      <OutType></OutType>
      <OutCode></OutCode>
      <OutStartDate></OutStartDate>
      <OutCollDate></OutCollDate>
    </DPOutcome>
  </LearnerDestinationandProgression>
</Destinations>

There are a large number of LearnerDestinationandProgression elements within the parent node.

I am getting the following error:

ORA-19112: error raised during evaluation: XVM-01155: [XUDY0027] Invalid target expression ORA-06512: at line 286

Any help would be much appreciated.

Thanks.

Arkadiusz Łukasiewicz Arkadiusz Łukasiewicz · Accepted Answer · 2017-10-31T09:09:34

XUDY0027 - means. you are trying to insert something into null.

Your xmls have different namespace. 1) Message has xmlns="SFA/ILR/2016-17"
2) Destinations has xmlns="some default url for oracle"

When you are processing both xmls in xmlquery, this expression $ilr/Message returns null. Because in default namespace there is no element Message.

Fast fix is: * - any namespace;

insert nodes $dp_out/Destinations as last into $ilr/*:Message

More elegant way is namespace declaration.

xquery version "1.0";
declare namespace ns="SFA/ILR/2016-17";
copy $ilr := .
modify
    insert nodes $dp_out/Destinations  as last into $ilr/ns:Message
return $ilr
'

PL/SQL Using XMLQuery/XQuery to insert elements from one XML document inside another at a specific point

1 Answers